SQL FIRST_VALUE

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

Source Table 1

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]
SQL FIRST_VALUE without Partition By Clause 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 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
Example 3

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 OVER and Order By Clause Example 4

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]
SQL FIRST_VALUE With Partition By and Order By Clause 5
Categories SQL