SQL FIRST_VALUE( ) Function

January 27, 2023

The FIRST_VALUE () Function in SQL is a Value Window Function that is used to fetch the first value within the ordered partition of the set or the entire table when the partition is not defined.

ORDER BY ( ) clause is required while using this function as it is used to define the ordering with which we need the next row. PARTITION BY () is optional in the query.

Syntax for FIRST_VALUE( ) ( ) Function –

FIRST_VALUE(column_name) OVER(ORDER BY column1,column2,..ASC|DESC);

Demo Table –

Product Table

Category	Region	City	Country	Sales
Office Supplies	Central	Chicago	United States	15.696
Technology	East	New York City	United States	539.97
Office Supplies	East	New York City	United States	22.58
Office Supplies	East	Dover	United States	19.54
Office Supplies	East	Dover	United States	671.94
Technology	West	Aurora	United States	169.064
Office Supplies	West	Aurora	United States	168.624
Office Supplies	East	Long Beach	United States	16.52
Office Supplies	West	Pasadena	United States	31.92
Furniture	West	Pasadena	United States	433.568

Example – Consider the above Product Table and execute a query to fetch the first value of sales from the current row value using the FIRST () Function.

SELECT Category,Region,City,[Country/Region],Sales , FIRST_VALUE (Sales) OVER(PARTITION BY Category ORDER BY Sales ) AS FIRST FROM Products;

Here is a small section of output after executing the above query –

Category	Region	City	Country	Sales	LEAD
Furniture	West	Pasadena	United States	433.568	433.568
Office Supplies	Central	Chicago	United States	15.696	15.696
Office Supplies	East	Long Beach	United States	16.52	15.696
Office Supplies	East	Dover	United States	19.54	15.696
Office Supplies	East	New York City	United States	22.58	15.696
Office Supplies	West	Pasadena	United States	31.92	15.696
Office Supplies	West	Aurora	United States	168.624	15.696
Office Supplies	East	Dover	United States	671.94	15.696
Technology	West	Aurora	United States	169.064	169.064
Technology	East	New York City	United States	539.97	169.064