PostgreSQL return multiple rows with DISTINCT though only latest date per second column

3
votes

Lets says I have the following database table (date truncated for example only, two 'id_' preix columns join with other tables)...

+-----------+---------+------+--------------------+-------+

| id_table1 | id_tab2 | date | description        | price |

+-----------+---------+------+--------------------+-------+

| 1         | 11      | 2014 | man-eating-waffles | 1.46  |

+-----------+---------+------+--------------------+-------+

| 2         | 22      | 2014 | Flying Shoes       | 8.99  |

+-----------+---------+------+--------------------+-------+

| 3         | 44      | 2015 | Flying Shoes       | 12.99 |
+-----------+---------+------+--------------------+-------+

...and I have a query like the following...

SELECT id, date, description FROM inventory ORDER BY date ASC;

How do I SELECT all the descriptions, but only once each while simultaneously only the latest year for that description? So I need the database query to return the first and last row from the sample data above; the second it not returned because the last row has a later date.

2
sqlpostgresqlselectgreatest-n-per-group

2 Answers

6
votes

Postgres has something called distinct on. This is usually more efficient than using window functions. So, an alternative method would be:

SELECT distinct on (description) id, date, description
FROM inventory
ORDER BY description, date desc;
5
votes

The row_number window function should do the trick:

SELECT  id, date, description 
FROM    (SELECT id, date, description, 
                ROW_NUMBER() OVER (PARTITION BY description 
                                   ORDER BY date DESC) AS rn
         FROM   inventory) t
WHERE    rn = 1
ORDER BY date ASC;