Often in PostgreSQL you may want to select the last row per group.
You can use the following basic syntax to do so:
SELECT DISTINCT ON (name)
productID, name, date_sold
FROM products
ORDER BY name, date_sold DESC;
This particular example will select the last row for each unique value in the productID column of the table named products, in which the “last” row refers to the most recent record based on the date in the date_sold column.
Note that we use the DISTINCT ON operator along with the column name that we’d like to return only one row for in parentheses.
The following example shows how to use this syntax in practice.
Example: How to Select Last Row per Group in PostgreSQL
Suppose that we create the following table named products that contains information about various products sold by some company on various dates:
-- create table named 'products'
CREATE TABLE products (
productID INTEGER PRIMARY KEY,
name TEXT,
date_sold DATE
);
-- add rows to the table
INSERT INTO products VALUES (0001, 'A', '2024-09-11');
INSERT INTO products VALUES (0002, 'A', '2024-09-12');
INSERT INTO products VALUES (0003, 'B', '2024-09-10');
INSERT INTO products VALUES (0004, 'B', '2024-09-15');
INSERT INTO products VALUES (0005, 'C', '2024-09-18');
INSERT INTO products VALUES (0006, 'C', '2024-09-22');
-- view all rows in table
SELECT * FROM products;
productid | name | date_sold
-----------+------+------------
1 | A | 2024-09-11
2 | A | 2024-09-12
3 | B | 2024-09-10
4 | B | 2024-09-15
5 | C | 2024-09-18
6 | C | 2024-09-22
Notice that there are three distinct products in the name column of the table: A, B and C.
We can use the following syntax to select only the last row (i.e. the most recent date sold) for each product name:
SELECT DISTINCT ON (name)
productID, name, date_sold
FROM products
ORDER BY name, date_sold DESC;
productid | name | date_sold
-----------+------+------------
2 | A | 2024-09-12
4 | B | 2024-09-15
6 | C | 2024-09-22
The resulting table shows only the last row for each distinct product from the name column of the table.
Note that we used the DESC operator to specify that we wanted the dates in the date_sold column to be sorted in descending order.
Feel free to leave this operator out if you would instead like to order the dates in ascending order and return the oldest date for each product instead.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to INSERT If Not Exists Already
PostgreSQL: How to Calculate Difference Between Rows
PostgreSQL: How to SELECT DISTINCT on Multiple Columns
