SQL LAST_VALUE( ) Function

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

ORDER BY ( ) clause is required in the process of executing the query as using this function it’s easy to define the ordering with which we need the next row. PARTITION BY () is optional in the process of executing the query.

  • Syntax for LAST_VALUE( ) Function –

LAST_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 mentioned above and execute a query to fetch the first value of sales from the current row value using the LAST () Function.

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

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

 

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.