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 paid employee from the entire 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 paid employee in each Department, and if more than one employee for any department 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(PARTITION BY EMP_DEPT ORDER BY EMP_SALARY DESC, EMP_NAME ) 
AS FIRSTVALUE
FROM EMP FIRST_VALUE

As you can see, It returns Highest paid employee name in each department against each rows.

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()

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 1,715 total views,  1 views today