sql_func_last

SQL: Retrieving the Last Record

Simulating a LAST() Function

Standard SQL does not have a LAST() aggregate function that directly returns the last value of a column in the same way MAX() returns the maximum value. The concept of ‘last’ usually implies an order.

To get the ‘last’ record or the value from the ‘last’ record in a specific order, you typically combine ordering (ORDER BY) with a clause that limits the result set to one row (LIMIT, TOP, FETCH FIRST).

The MS Access database system has a LAST() function, but this is not portable to other SQL databases like SQL Server, MySQL, PostgreSQL, or Oracle.

Common Techniques to Get the Last Record

The general approach involves sorting the data in descending order based on a relevant column (e.g., an ID, a timestamp) and then picking the first row from this sorted set.

MySQL / PostgreSQL / SQLite Syntax:

SELECT column_name
FROM table_name
ORDER BY sort_criteria_column DESC
LIMIT 1;

SQL Server Syntax:

SELECT TOP 1 column_name
FROM table_name
ORDER BY sort_criteria_column DESC;

Oracle Syntax (12c and later using FETCH):

SELECT column_name
FROM table_name
ORDER BY sort_criteria_column DESC
FETCH FIRST 1 ROW ONLY;

Oracle Syntax (pre-12c using ROWNUM and subquery):

SELECT column_name
FROM (
  SELECT column_name
  FROM table_name
  ORDER BY sort_criteria_column DESC
)
WHERE ROWNUM = 1;

Demo Table Context

For these examples, consider an Orders table with columns like OrderID (an auto-incrementing primary key), CustomerID, OrderDate, and OrderAmount.

Examples: Retrieving the Last Order

Example (MySQL / PostgreSQL / SQLite): Get the most recent order based on OrderID.

SELECT OrderID, CustomerID, OrderDate
FROM Orders
ORDER BY OrderID DESC
LIMIT 1;

Example (SQL Server): Get the most recent order.

SELECT TOP 1 OrderID, CustomerID, OrderDate
FROM Orders
ORDER BY OrderID DESC;

Example (Oracle 12c+): Get the most recent order.

SELECT OrderID, CustomerID, OrderDate
FROM Orders
ORDER BY OrderID DESC
FETCH FIRST 1 ROW ONLY;

If ‘last’ refers to a different criterion, like the latest OrderDate which might not align with OrderID if dates can be manually set:

SELECT OrderID, CustomerID, OrderDate
FROM Orders
ORDER BY OrderDate DESC, OrderID DESC -- Secondary sort for ties in OrderDate
LIMIT 1; -- (Syntax for MySQL/PostgreSQL)

Considerations for ‘Last’

Defining ‘Last’: The meaning of ‘last’ is crucial. Is it the highest ID, the latest date, or something else? Your ORDER BY clause must reflect this definition.

Ties: If multiple records could qualify as ‘last’ (e.g., multiple orders at the exact same latest timestamp), the queries above will pick one arbitrarily (or based on further implicit ordering if not specified). If you need all ties, you’d use a different approach, possibly involving subqueries with MAX() or window functions.

Window Functions: For more complex ‘last value per group’ scenarios, SQL window functions like LAST_VALUE() (though its usage can be tricky and often ROW_NUMBER() is preferred) or ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ... DESC) are powerful tools in modern SQL databases.

For further reading, search for ‘SQL get last record per group’ for more advanced scenarios.