{"id":662,"date":"2024-12-05T13:33:06","date_gmt":"2024-12-05T06:33:06","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=662"},"modified":"2025-01-02T20:08:01","modified_gmt":"2025-01-02T13:08:01","slug":"postgresql-distinct-on","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-distinct-on\/","title":{"rendered":"PostgreSQL DISTINCT ON"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to use the PostgreSQL DISTINCT ON<\/code> clause to select distinct rows from a table.<\/p>\n\n\n\n

Getting started with the PostgreSQL SELECT DISTINCT ON clause #<\/a><\/h2>\n\n\n\n

In PostgreSQL, a SELECT DISTINCT ON<\/code> clause allows you to select a row for each group defined by the ON<\/code> clause.<\/p>\n\n\n\n

Here’s the syntax of the DISTINCT ON<\/code> clause:<\/p>\n\n\n

SELECT<\/span> DISTINCT<\/span> ON<\/span> (column1), column2, column3\nFROM<\/span> table_name<\/span>\nORDER<\/span> BY<\/span> column1, column2, column3;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
First, specify a column (column1<\/code>) inside the parentheses of the ON<\/code> keyword. The ON<\/code> keyword will group rows in the table_name<\/code> by the values in the column1<\/code> into distinct groups. The SELECT<\/code> clause may include other columns (column2<\/code>, column3<\/code>) or expressions<\/code>.<\/p>\n\n\n\n
Note that SELECT DISTINCT ON<\/code> is PostgreSQL’s extension to SQL standard. So it may not be available in other database systems.<\/p>\n\n\n\n
PostgreSQL SELECT DISTINCT ON clause example #<\/a><\/h2>\n\n\n\n
Suppose you have the following inventories<\/code> table that includes product name, brand, warehouse, and quantity:<\/p>\n\n\n\n
id<\/th>product_name<\/th>brand<\/th>warehouse<\/th>quantity<\/th><\/tr><\/thead>
1<\/td>iPhone 16<\/td>Apple<\/td>San Jose<\/td>50<\/td><\/tr>
2<\/td>iPhone 16<\/td>Apple<\/td>San Francisco<\/td>30<\/td><\/tr>
3<\/td>iPhone 16 Pro<\/td>Apple<\/td>San Jose<\/td>40<\/td><\/tr>
4<\/td>iPhone 16 Pro<\/td>Apple<\/td>San Francisco<\/td>20<\/td><\/tr>
5<\/td>iPhone 16 Pro Max<\/td>Apple<\/td>San Francisco<\/td>25<\/td><\/tr>
6<\/td>Galaxy S22<\/td>Samsung<\/td>San Jose<\/td>50<\/td><\/tr>
7<\/td>Galaxy S22<\/td>Samsung<\/td>San Francisco<\/td>30<\/td><\/tr>
8<\/td>Galaxy S22 Ultra<\/td>Samsung<\/td>San Francisco<\/td>20<\/td><\/tr>
9<\/td>Galaxy Z Fold 4<\/td>Samsung<\/td>San Jose<\/td>55<\/td><\/tr>
10<\/td>Galaxy Z Fold 4<\/td>Samsung<\/td>San Francisco<\/td>35<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
SQL Script for creating the inventories table and inserting data into it<\/summary>
CREATE<\/span> TABLE<\/span> inventories(\n   id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n   product_name VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>,\n   brand VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>,\n   warehouse VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>,\n   quantity INT<\/span> NOT<\/span> NULL<\/span>\n);\n\nINSERT<\/span> INTO<\/span> inventories (product_name, brand, warehouse, quantity) \nVALUES<\/span>\n('iPhone 16'<\/span>, 'Apple'<\/span>, 'San Jose'<\/span>, 50<\/span>),\n('iPhone 16'<\/span>, 'Apple'<\/span>, 'San Francisco'<\/span>, 30<\/span>),\n('iPhone 16 Pro'<\/span>, 'Apple'<\/span>, 'San Jose'<\/span>, 40<\/span>),\n('iPhone 16 Pro'<\/span>, 'Apple'<\/span>, 'San Francisco'<\/span>, 20<\/span>),\n('iPhone 16 Pro Max'<\/span>, 'Apple'<\/span>, 'San Francisco'<\/span>, 25<\/span>),\n('Galaxy S22'<\/span>, 'Samsung'<\/span>, 'San Jose'<\/span>, 50<\/span>),\n('Galaxy S22'<\/span>, 'Samsung'<\/span>, 'San Francisco'<\/span>, 30<\/span>),\n('Galaxy S22 Ultra'<\/span>, 'Samsung'<\/span>, 'San Francisco'<\/span>, 20<\/span>),\n('Galaxy Z Fold 4'<\/span>, 'Samsung'<\/span>, 'San Jose'<\/span>, 55<\/span>),\n('Galaxy Z Fold 4'<\/span>, 'Samsung'<\/span>, 'San Francisco'<\/span>, 35<\/span>);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre><\/details>\n\n\n\n
The following statement uses the SELECT DISTINCT ON<\/code> clause to select product name, brand, and the highest inventory quantity for each product:<\/p>\n\n\n
SELECT<\/span> DISTINCT<\/span>\n  ON<\/span> (product_name) product_name,\n  brand,\n  quantity\nFROM<\/span>\n  inventories\nORDER<\/span> BY<\/span>\n  product_name,\n  quantity DESC<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Try it<\/a><\/p>\n\n\n\n
Output:<\/p>\n\n\n
   product_name    |  brand  | quantity\n-------------------+---------+----------\n Galaxy S22        | Samsung |       50\n Galaxy S22 Ultra  | Samsung |       20\n Galaxy Z Fold 4   | Samsung |       55\n iPhone 16         | Apple   |       50\n iPhone 16 Pro     | Apple   |       40\n iPhone 16 Pro Max | Apple   |       25<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example:<\/p>\n\n\n\n
First, the ON<\/code> clause groups the rows in the inventories<\/code> table into six groups by product name:<\/p>\n\n\n
ON<\/span> (product_name) product_name<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this clause, we specify the product_name<\/code> as a column alias to use in the ORDER BY<\/code> clause for sorting.<\/p>\n\n\n\n
Second, the ORDER BY<\/code> clause sorts the rows in each group by product name and quantity from high to low and selects the first row in each group.<\/p>\n\n\n\n
If you want to select the product name, brand, and the lowest inventory quantity, you can change the ORDER CLAUSE<\/code> to place the products with the lowest quantity first:<\/p>\n\n\n
SELECT<\/span> DISTINCT<\/span>\n  ON<\/span> (product_name) product_name,\n  brand,\n  quantity\nFROM<\/span>\n  inventories\nORDER<\/span> BY<\/span>\n  product_name,\n  quantity;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
   product_name    |  brand  | quantity\n-------------------+---------+----------\n Galaxy S22        | Samsung |       30\n Galaxy S22 Ultra  | Samsung |       20\n Galaxy Z Fold 4   | Samsung |       35\n iPhone 16         | Apple   |       30\n iPhone 16 Pro     | Apple   |       20\n iPhone 16 Pro Max | Apple   |       25<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Second, provide the column defined in the ON<\/code> clause as the first column in the ORDER BY<\/a><\/code> clause. The ORDER BY<\/code> clause sorts rows in each distinct group by column1<\/code> and keeps the first row only.<\/p>\n\n\n\n