SQL FIRST_VALUE

The SQL FIRST_VALUE is one of the Analytic Function. This FIRST_VALUE function will return the First Value in each partition. The basic syntax of the FIRST_VALUE in SQL Server is as shown below:

SELECT FIRST VALUE([Scalar Expression]) 
                  OVER (
                         PARTITION_BY_Clause 
                         ORDER_BY_Clause
                        )
FROM [Source]

For this FIRST_VALUE demonstration, we are going to use the below-shown data

SQL FIRST_VALUE 1

SQL FIRST_VALUE without Partition By Clause

What happens if we avoid the Partition By Clause in the FIRST_VALUE 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]
SQL FIRST_VALUE 2

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 sort the Employee table based on the Hire Date in the Ascending order

ORDER BY [HireDate] ASC

Let me show you the same

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
  ORDER BY [HireDate] ASC
SQL FIRST_VALUE 3

From the above screenshot you can observe that, Employee table has Occupation = Professional, and Sales = 3578.27 as the first column values. Now, SQL FIRST_VALUE function in the below query return 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 4

FIRST_VALUE with Partition By Example

The FIRST_VALUE in SQL Server allows you to select the first value in each partition. The following query partition the data by Occupation and sort the data in Descending order by their Sales. Then select the first [First Name] value or first value in each partition.

USE [SQL Tutorial]
GO
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,FIRST_VALUE([FirstName]) OVER (
                                       PARTITION BY [Occupation] 
    				       ORDER BY [Sales] DESC
				      ) AS BestCustomer   
      ,[HireDate]
  FROM [Employee]
SQL FIRST_VALUE 5