PostgreSQL DISTINCT ON

Summary: in this tutorial, you’ll learn how to use the PostgreSQL DISTINCT ON clause to select distinct rows from a table.

Getting started with the PostgreSQL SELECT DISTINCT ON clause #

In PostgreSQL, a SELECT DISTINCT ON clause allows you to select a row for each group defined by the ON clause.

Here’s the syntax of the DISTINCT ON clause:

SELECT DISTINCT ON (column1), column2, column3
FROM table_name
ORDER BY column1, column2, column3;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

First, specify a column (column1) inside the parentheses of the ON keyword. The ON keyword will group rows in the table_name by the values in the column1 into distinct groups. The SELECT clause may include other columns (column2, column3) or expressions.

Second, provide the column defined in the ON clause as the first column in the ORDER BY clause. The ORDER BY clause sorts rows in each distinct group by column1 and keeps the first row only.

Note that SELECT DISTINCT ON is PostgreSQL’s extension to SQL standard. So it may not be available in other database systems.

PostgreSQL SELECT DISTINCT ON clause example #

Suppose you have the following inventories table that includes product name, brand, warehouse, and quantity:

CREATE TABLE inventories(
   id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   product_name VARCHAR(50) NOT NULL,
   brand VARCHAR(50) NOT NULL,
   warehouse VARCHAR(50) NOT NULL,
   quantity INT NOT NULL
);

INSERT INTO inventories (product_name, brand, warehouse, quantity) 
VALUES
('iPhone 16', 'Apple', 'San Jose', 50),
('iPhone 16', 'Apple', 'San Francisco', 30),
('iPhone 16 Pro', 'Apple', 'San Jose', 40),
('iPhone 16 Pro', 'Apple', 'San Francisco', 20),
('iPhone 16 Pro Max', 'Apple', 'San Francisco', 25),
('Galaxy S22', 'Samsung', 'San Jose', 50),
('Galaxy S22', 'Samsung', 'San Francisco', 30),
('Galaxy S22 Ultra', 'Samsung', 'San Francisco', 20),
('Galaxy Z Fold 4', 'Samsung', 'San Jose', 55),
('Galaxy Z Fold 4', 'Samsung', 'San Francisco', 35);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following statement uses the SELECT DISTINCT ON clause to select product name, brand, and the highest inventory quantity for each product:

SELECT DISTINCT
  ON (product_name) product_name,
  brand,
  quantity
FROM
  inventories
ORDER BY
  product_name,
  quantity DESC;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   product_name    |  brand  | quantity
-------------------+---------+----------
 Galaxy S22        | Samsung |       50
 Galaxy S22 Ultra  | Samsung |       20
 Galaxy Z Fold 4   | Samsung |       55
 iPhone 16         | Apple   |       50
 iPhone 16 Pro     | Apple   |       40
 iPhone 16 Pro Max | Apple   |       25Code language: plaintext (plaintext)

In this example:

First, the ON clause groups the rows in the inventories table into six groups by product name:

ON (product_name) product_nameCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this clause, we specify the product_name as a column alias to use in the ORDER BY clause for sorting.

Second, the ORDER BY clause sorts the rows in each group by product name and quantity from high to low and selects the first row in each group.

If you want to select the product name, brand, and the lowest inventory quantity, you can change the ORDER CLAUSE to place the products with the lowest quantity first:

SELECT DISTINCT
  ON (product_name) product_name,
  brand,
  quantity
FROM
  inventories
ORDER BY
  product_name,
  quantity;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   product_name    |  brand  | quantity
-------------------+---------+----------
 Galaxy S22        | Samsung |       30
 Galaxy S22 Ultra  | Samsung |       20
 Galaxy Z Fold 4   | Samsung |       35
 iPhone 16         | Apple   |       30
 iPhone 16 Pro     | Apple   |       20
 iPhone 16 Pro Max | Apple   |       25Code language: plaintext (plaintext)

Summary #

• Use the SELECT DISTINCT ON to group rows into distinct groups and retain the first row in each group.
• Use the ORDER BY clause to define the row to keep in each group and include it in the final result set.

Quiz #