Clever Geek Handbook

PostgreSQL returns multiple rows with DISTINCT although only the last date per second of the column

Lets say that I have the following database table (only the date is truncated, for example two preix columns id_ are joining 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 request like the following ...

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

Like SELECT

all descriptions, but only once each at a time and only the last year for this description? So I need a database query to return the first and last row from the sampled data above; the second is not returned because the last line is at a later date.

+3


source to share


2 answers


Postgres has something called distinct on

. This is usually more efficient than using window functions. So an alternative method:



SELECT distinct on (description) id, date, description
FROM inventory
ORDER BY description, date desc;
+6


source


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;
+5


source






More articles:


All Articles