SQL - how to order by date and select distinct

Question

I have a table named review.

+-------------+-------------------------+
| category_id |          date           |
+-------------+-------------------------+
|           4 | 2020-10-29 00:33:23.485 |
|           4 | 2020-11-23 17:23:14.686 |
|           3 | 2020-11-23 17:28:59.032 |
+-------------+-------------------------+

I want to order by date and only get unique category_id.

example [1]
ordering by date desc (newest first), should return:

+-------------+-------------------------+
| category_id |          date           |
+-------------+-------------------------+
|           3 | 2020-11-23 17:28:59.032 |
|           4 | 2020-11-23 17:23:14.686 |
+-------------+-------------------------+

exmaple [2]
and ordering by date asc (oldest first), should return:

+-------------+-------------------------+
| category_id |          date           |
+-------------+-------------------------+
|           4 | 2020-10-29 00:33:23.485 |
|           3 | 2020-11-23 17:28:59.032 |
+-------------+-------------------------+

I've tried writing multiple queries for this. Something like this comes close, however, it doesn't order by date:
SELECT DISTINCT ON (category_id) category_id, date FROM review ORDER BY category_id, date ASC results in

+-------------+-------------------------+
| category_id |          date           |
+-------------+-------------------------+
|           3 | 2020-11-23 17:28:59.032 |
|           4 | 2020-10-29 00:33:23.485 |
+-------------+-------------------------+

yet I would like it to look like in example [2]

I am using PostgreSQL.

Wrap it in a cte or use `group by` with `min(date)` – shawnt00 Nov 23 '20 at 19:36 — shawnt00, Nov 23 '20 at 19:36

score 5 · Accepted Answer · answered Nov 23 '20 at 19:37

Your way is correct. You need the "wrong" order to get the correct date value per category_id.

You need to correct this order with an additional ORDER BY step:

SELECT
    *
FROM (
    SELECT DISTINCT ON (category_id) 
        category_id, 
        date 
    FROM review 
    ORDER BY category_id, date ASC
) s
ORDER BY date ASC

SQL - how to order by date and select distinct

1 Answers1