DuckDB is an in-process SQL OLAP database management system designed for analytical workloads. Among its many functions is one called last(). This function returns the last value in a column.

Let’s take a look at the last() function in DuckDB.

What is the last() Function?

The last() function is an aggregate function in DuckDB that returns the last value of a specified column within a group of rows. Unlike some other aggregate functions, such as sum() or avg(), which perform calculations across all values in a column, last() focuses on retrieving a single value: the last one in the sequence.

Syntax

The syntax for the last() function is straightforward:

last(column_name)

Here, column_name refers to the column from which you want to retrieve the last value.

Examples

Below are some examples that demonstrate how the last() function works.

Sample Data

First, we’ll create and populate a table for the examples:

-- Create the sales table
CREATE TABLE sales (
    id INTEGER,
    product VARCHAR,
    amount INTEGER
);

-- Insert sample data into the sales table
INSERT INTO sales (id, product, amount) VALUES
(1, 'A', 100),
(2, 'B', 200),
(3, 'C', 300),
(4, 'A', 150),
(5, 'B', 250),
(6, 'C', 350),
(7, 'A', 120),
(8, 'B', 220),
(9, 'C', 320);

-- Query to verify the data
SELECT * FROM sales;

Output:

+----+---------+--------+
| id | product | amount |
+----+---------+--------+
| 1  | A       | 100    |
| 2  | B       | 200    |
| 3  | C       | 300    |
| 4  | A       | 150    |
| 5  | B       | 250    |
| 6  | C       | 350    |
| 7  | A       | 120    |
| 8  | B       | 220    |
| 9  | C       | 320    |
+----+---------+--------+

Example without Ordering

Here’s a basic example that uses the last() function in a query that doesn’t specify any particular ordering:

SELECT last(amount) 
FROM sales;

Result:

+----------------+
| "last"(amount) |
+----------------+
| 320            |
+----------------+

The result is 320 because 320 is the value in the last row of the amount column based on the default order.

Example with Ordering

The result of the last() function is directly tied to the order of the rows in the dataset. We can use the ORDER BY clause to explicitly specify an order:

SELECT 
    last(amount ORDER BY id DESC) 
FROM sales;

Result:

+---------------------------------+
| "last"(amount ORDER BY id DESC) |
+---------------------------------+
| 100                             |
+---------------------------------+

The last() function returns 100 because it is the value in the last row of the amount column after reordering.

As demonstrated, the order of rows can significantly impact the result of the last() function. If no specific order is defined, DuckDB will use the natural order of the rows, which may not always align with your expectations. To ensure accurate results, always use an ORDER BY clause.

Example with WHERE Clause

Here’s an example that uses a WHERE clause:

SELECT last(amount) 
FROM sales
WHERE product = 'A';

Result:

+----------------+
| "last"(amount) |
+----------------+
| 120            |
+----------------+

And let’s add an ORDER BY clause to that:

SELECT 
    last(amount ORDER BY id DESC) 
FROM sales
WHERE product = 'A';

Result:

+---------------------------------+
| "last"(amount ORDER BY id DESC) |
+---------------------------------+
| 100                             |
+---------------------------------+

Example with Grouping

The last() function can also be used in conjunction with the GROUP BY clause. In such cases, it will return the last value within each group, based on the specified order.

For example:

SELECT 
    product, 
    last(amount ORDER BY id) 
FROM sales 
GROUP BY product;

Result:

+---------+----------------------------+
| product | "last"(amount ORDER BY id) |
+---------+----------------------------+
| A       | 120                        |
| B       | 220                        |
| C       | 320                        |
+---------+----------------------------+

And here it is when we change the order to descending order:

SELECT 
    product, 
    last(amount ORDER BY id DESC) 
FROM sales 
GROUP BY product;

Result:

+---------+---------------------------------+
| product | "last"(amount ORDER BY id DESC) |
+---------+---------------------------------+
| A       | 100                             |
| B       | 200                             |
| C       | 300                             |
+---------+---------------------------------+

Example of Handling NULL Values

When the last value in the column is NULL, the last() function returns NULL:

-- Set the last value to null
UPDATE sales SET amount = null 
WHERE id = 9;

-- Get the last value
SELECT 
    last(amount ORDER BY id ASC) 
FROM sales;

Result:

+--------------------------------+
| "last"(amount ORDER BY id ASC) |
+--------------------------------+
| null                           |
+--------------------------------+