The SQL Server FIRST_VALUE is one of the Analytic functions. This FIRST_VALUE function will return the First Value in each partition, and the basic syntax of it is as shown below:
SELECT FIRST VALUE([Scalar Expression]) OVER ( PARTITION_BY_Clause ORDER_BY_Clause ) FROM [Source]
For this SQL Server FIRST_VALUE demonstration, we are going to use the below-shown data
SQL FIRST_VALUE without Partition By Clause
What happens if we avoid the Partition By Clause in this Function?
SELECT [FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,[HireDate] ,FIRST_VALUE([Sales]) OVER (ORDER BY [HireDate] ASC) AS LeastSale ,FIRST_VALUE([Occupation]) OVER (ORDER BY [HireDate] ASC) AS Profession FROM [Employee]
Above FIRST_VALUE code is a common select statement, and we are writing the first value of Sales and Profession based on the Hire Date column.
The below SQL Server statement sorts the Employee table based on the HireDate in the Ascending. Let me show you the same.
SELECT [FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,[HireDate] FROM [Employee] ORDER BY [HireDate] ASC
The above screenshot shows that the Employee table has Occupation = Professional and Sales = 3578.27 as the first column values. Now, the SQL FIRST_VALUE function in the below query returns those values as the output.
,FIRST_VALUE([Sales]) OVER (ORDER BY [HireDate] ASC) AS LeastSale ,FIRST_VALUE([Occupation]) OVER (ORDER BY [HireDate] ASC) AS Profession
Let me change the ORDER BY CLAUSE to descending order to clarify further.
SELECT [FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,[HireDate] ,FIRST_VALUE([Sales]) OVER (ORDER BY [HireDate] ASC) AS LeastSale ,FIRST_VALUE([Occupation]) OVER (ORDER BY [HireDate] ASC) AS Profession FROM [Employee]
SQL FIRST_VALUE with Partition By Example
The FIRST_VALUE allows you to select the first value in each partition. The following query partition the data by Occupation and sort the data in Descending by their Sales. Then select the first [First Name] value or first value in each partition.
SELECT [FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,FIRST_VALUE([FirstName]) OVER ( PARTITION BY [Occupation] ORDER BY [Sales] DESC ) AS BestCustomer ,[HireDate] FROM [Employee]