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:
- What is RDBMS (Relational Database Management System)?
- SQL SELECT Statement
- SQL INSERT INTO SELECT Statement
- SQL ALL Operator
- SQL WHERE Clause
- SQL AND Operator
- SQL OR Operator
Follow us on Twitter, Facebook, Linkedin, and Tableau Public to stay updated with our latest blog and what’s new in Tableau.
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.
