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
Last activity: Apr 2, 2023, 09:35 AM