PostgreSQL – SELECT DISTINCT ON Ordered by Another Column

distinctgreatest-n-per-grouporder-bypostgresqlpostgresql-9.6

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

Best Answer

You can still use DISTINCT ON. Just wrap it into an outer query to sort to your needs. See:

SELECT *
FROM  (
   SELECT DISTINCT ON (col1)
          col1, col2, col3
   FROM   test
   ORDER  BY col1, col3 DESC
   ) sub
ORDER  BY col3 DESC, col2;

Assuming that col2 functionally depends on col1, so we can ignore it in DISTINCT ON and ORDER BY of the inner query. But I added it to the outer ORDER BY as meaningful tiebreaker. If col2 not unique without col1, you might append col1 additionally.

Assuming col3 is defined NOT NULL. Else append NULLS LAST:

With only few rows per (col1), this is typically the fastest solution. See:

db<>fiddle here

A subquery with the window function row_number() (like Vérace suggested) is a valid alternative, but typically slower. I have done many tests, but try yourself. It has to sort twice, just like DISTINCT ON (which may switch to a hashing algorithm internally if that's expected to be faster), but it keeps all rows after the inner query, adding needless cost. Either way, you don't need ORDER BY in the inner query:

SELECT col1, col2, col3
FROM  (
   SELECT col1, col2, col3
       ,  row_number() OVER (PARTITION BY col1 ORDER BY col3 DESC) AS rn
   FROM   test
   ) sub
WHERE  rn = 1
ORDER  BY col3 DESC, col2;

And don't use a CTE if you don't need it. It's typically considerably more expensive (up until Postgres 12, where this was fixed, mostly).

For many rows per col1, indexing becomes much more important, and there are typically much faster alternatives. See:

Aside, unlike Oracle or SQL Server, PostgreSQL does not use the term "analytic functions" for window functions. (What's "analytic" about those functions?)

Related Question