SQL Server FIRST_VALUE function

SQL Server FIRST_VALUE function is a analytic function which returns the first value in an ordered set of values. It Introduced in SQL Server 2012.

Syntax 

LAST_VALUE(Column_Name) OVER ( [partition_by_clause col1, col2,.] 
ORDER BY Col1, Col2, ...)

Column_Name
Is the value to be returned.  

OVER ( [partition_by_clause col1, col2,.] ORDER BY Col1, Col2, … )

partition_by_clause divides the rows of the result sets into partitions to which the function applies. It is an optional.

order_by_clause specify the order in which the operation is performed. it is required.

 Lets look at an example of FIRST_VALUE in SQL Server.

First, we create a sample table named as EMP, and insert some records in the table.

CREATE TABLE dbo.EMP
(EMPID INT PRIMARY KEY, 
EMP_DEPT VARCHAR(50), 
EMP_NAME VARCHAR(30), 
EMP_SALARY NUMERIC(9,2))

INSERT INTO dbo.EMP (
EMPID, EMP_DEPT, EMP_NAME, EMP_SALARY)
VALUES(
101, 'PRODUCTION', 'RAJAT M', 75000.00),
(102, 'PRODUCTION', 'MUKESH BHATIA', 70000.00),
(103, 'PRODUCTION', 'MUSKAN MEHTA', 75000.00),
(104, 'SALES', 'ROHAN B NARAYA', 45000.00),
(105, 'SALES', 'SUSHIL DAS', 40000.00),
(106, 'SALES', 'MANISH', 45000.00),
(107, 'PRODUCTION', 'RAJESH SINGH', 78000.00),
(108, 'HR', 'MOHIN KHAN',50000.00),
(109, 'HR', 'SUSHANT K SINGH', 55000.00),
(110, 'HR', 'LAKSHYA RAWAT', 55000.00),
(111, 'PRODUCTION', 'MANOJ KUMAR', 75000.00),
(112, 'SALES', 'SUJOY M', 40000.00),
(113, 'LOGISTIC', 'VINAY AGARWAL', 35000.00),
(114, 'LOGISTIC', 'MUSTAKIM M', 35000.00),
(115, 'LOGISTIC', 'VIJAY KUMAWAT', 45000.00)

Basic use of FIRST_VALUE Function

In the following example, FIRST_VALUE function returns the name of the highest salary paid employee from table, and if more than one employee having same salary then sort the record based on employee name in ascending order.

SELECT EMPID, EMP_DEPT, EMP_NAME, EMP_SALARY, 
FIRST_VALUE(EMP_NAME) OVER (ORDER BY EMP_SALARY DESC, EMP_NAME ) AS FIRSTVALUE 
FROM EMP FIRST_VALUE

As you can see, the output of above statement, it returns the name of highest paid employee in column FIRSTVALUE against each rows.

FIRST_VALUE function with PARTITION clause

In the following example,  FIRST_VALUE function returns the name of the highest salary paid employee in each department, and if more than one employee for any department having same salary then sort the records based on employee’s name in ascending order.

SELECT EMPID, EMP_DEPT, EMP_NAME, EMP_SALARY,
FIRST_VALUE(EMP_NAME)OVER(PARTITION BY EMP_DEPT ORDER BY EMP_SALARY DESC, EMP_NAME ) 
AS FIRSTVALUE
FROM EMP FIRST_VALUE

As you can see, it returns the name of highest salary paid employee for each department.

Also in sales department there are two employee who is getting same salary that is 45000, so sorting is done based on the their name in ascending order that’s why MANISH comes first.

Also Read..

LEAD()

LAG()