SQL LAST

What is SQL LAST?

Returns the last value in an ordered result set or partition.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL LAST: LAST aggregate: Access, Informix, Firebird LAST_VALUE window: PostgreSQL, MySQL 8.0+, SQL Server 2012+, Oracle 12c+, SQLite 3.28+

SQL LAST Full Explanation

LAST is a non-standard aggregate function that returns the final value from a set of rows after ordering. Although not part of core ANSI SQL, it exists in Access, Informix, Firebird, and other engines. In standard SQL the same effect is achieved with the window function LAST_VALUE. Both approaches require a deterministic ORDER BY clause; without it, the concept of "last" is undefined. LAST (or LAST_VALUE) can also operate inside a partition, giving the last value within each group. Be aware that NULL handling follows normal ordering rules unless you use NULLS FIRST | LAST.

SQL LAST Syntax

-- Non-standard aggregate form
SELECT LAST(column_name) AS last_val
FROM   table_name
ORDER  BY sort_column;

-- Standard window form
SELECT LAST_VALUE(column_name) OVER (
         ORDER BY sort_column
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_val
FROM   table_name;

SQL LAST Parameters

column_name (any) - Column to evaluate
sort_column (any) - Column or expression that defines row order

Example Queries Using SQL LAST

-- 1. Last order amount in entire table (Access / Informix)
SELECT LAST(total_amount) AS last_amount
FROM   orders
ORDER  BY order_date;

-- 2. Last order amount per customer using window function (PostgreSQL, MySQL 8+)
SELECT customer_id,
       LAST_VALUE(total_amount) OVER (
         PARTITION BY customer_id
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_amount
FROM   orders;

Expected Output Using SQL LAST

Query returns the value of total_amount from the row that appears last after the ORDER BY evaluation; in the partitioned example each customer_id shows its own latest amount

Use Cases with SQL LAST

Fetch most recent transaction amount per user
Display latest status in audit tables
Derive closing balance at period end
Replace self-join patterns that locate the max(date) then rejoin

Common Mistakes with SQL LAST

Omitting ORDER BY and expecting a meaningful result
Forgetting ROWS clause in LAST_VALUE and getting current row instead of last
Assuming LAST exists in all databases
Misinterpreting NULL ordering when multiple NULLs are present

First Introduced In

SQL:2003 (LAST_VALUE window function)

Frequently Asked Questions

What does SQL LAST return?

It returns the value stored in the row that appears last after a specified ORDER BY. When used with PARTITION BY it gives the last value within each partition.

Can I use SQL LAST without ORDER BY?

You can syntactically, but the result is nondeterministic. Always supply ORDER BY to define what "last" means.

How do I get the last value in PostgreSQL?

Use LAST_VALUE(column) OVER (ORDER BY some_column ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING).

Why am I seeing the current row instead of the last row with LAST_VALUE?

Add the window frame clause ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Without it, LAST_VALUE defaults to the frame ending at the current row.