Sample Header Ad - 728x90

SELECT DISTINCT ON, ordered by another column

16 votes
2 answers
25768 views
Please consider the following table test:
CREATE TABLE test(col1 int, col2 varchar, col3 date);
INSERT INTO test VALUES
  (1,'abc','2015-09-10')
, (1,'abc','2015-09-11')
, (2,'xyz','2015-09-12')
, (2,'xyz','2015-09-13')
, (3,'tcs','2015-01-15')
, (3,'tcs','2015-01-18');
postgres=# select * from test; col1 | col2 | col3 ------+------+------------ 1 | abc | 2015-09-10 1 | abc | 2015-09-11 2 | xyz | 2015-09-12 2 | xyz | 2015-09-13 3 | tcs | 2015-01-15 3 | tcs | 2015-01-18 I'd like to have a returned set ordered by date desc: col1 | col2 | col3 ------+------+------------ 2 | xyz | 2015-09-13 1 | abc | 2015-09-11 3 | tcs | 2015-01-18 What I've managed to accomplish with distinct on: select distinct on (col1) col1, col2, col3 from test order by col1, col3 desc; col1 | col2 | col3 ------+------+------------ 1 | abc | 2015-09-11 2 | xyz | 2015-09-13 3 | tcs | 2015-01-18 And not what I need with having: select distinct on (col1) col1, col2, col3 from test group by col1, col2, col3 having col3 = max(col3) col1 | col2 | col3 ------+------+------------ 1 | abc | 2015-09-10 2 | xyz | 2015-09-13 3 | tcs | 2015-01-18
Asked by Luis (347 rep)
May 5, 2020, 08:43 PM
Last activity: Apr 2, 2023, 09:35 AM