FIRST_VALUE function in SQL Server

we will discuss FIRST_VALUE function in SQL Server

FIRST_VALUE function 

• Introduced in SQL Server 2012
• Retrieves the first value from the specified column
• ORDER BY clause is required
• PARTITION BY clause is optional

Syntax : FIRST_VALUE(Column_Name) OVER (ORDER BY Col1, Col2, ...)

FIRST_VALUE function example WITHOUT partitions : In the following example, FIRST_VALUE function returns the name of the lowest paid employee from the entire table.

SELECT Name, Gender, Salary,

FIRST_VALUE(Name) OVER (ORDER BY Salary) AS FirstValue

FROM Employees



FIRST_VALUE function example WITH partitions : In the following example, FIRST_VALUE function returns the name of the lowest paid employee from the respective partition.

SELECT Name, Gender, Salary,

FIRST_VALUE(Name) OVER (PARTITION BY Gender ORDER BY Salary) AS FirstValue

FROM Employees