Home » SQL Server FIRST_VALUE Function

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 TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 517 total views,  1 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.