SQL FIRST_VALUE( ) Function

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

 

Other SQL topics to check out:

Follow us on TwitterFacebookLinkedin, and Tableau Public to stay updated with our latest blog and what’s new in Tableau.

Automate data analysis pipeline and create report ready dashboards

If you are looking forward to getting your data pipeline built and setting up the dashboard for business intelligence, book a call now from here.