7

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
6

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?)

| improve this answer | |
  • Could you tell me (us) why the performance of DISTINCT ON is (so much) better than the other solutions? I ran EXPLAIN (ANALYZE BUFFERS) on the four and its performance was significantly better than the others. I'm puzzled as to why this should be the case - or is the answer buried deep in C code? – Vérace May 6 at 12:47
  • @Vérace: I haven't studied the actual C implementation (but I am sure it is optimized for the task). I assume the main reason for the performance difference is the one I mentioned above: the alternative with row_number() in a subquery ... keeps all rows after the inner query, adding needless cost. – Erwin Brandstetter May 12 at 2:12
  • @ypercubeᵀᴹ - thanks for your input here - but would you care to outline (or point me to links/posts here/other sites) which would illustrate this point? – Vérace May 12 at 12:11
  • @Vérace: I think I did just that at the bottom of my answer. – Erwin Brandstetter May 12 at 13:40
6

This is a classic greatest-n-per-group problem. They frequently arise in a whole host of areas and, like Analytic functions (see below) are well worth studying.

Nowadays, it is typically solved by using Analytic (aka Window) functions - see the fiddle here.

You can use this query -

WITH cte AS
(
  SELECT 
    ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS rn,
    col1, col2, col3 
  FROM test
  ORDER BY col3 DESC
)
SELECT * FROM cte 
WHERE rn = 1

Result -

rn  col1    col2    col3
1   2   xyz     2015-09-13
1   1   abc     2015-09-11
1   3   tcs     2015-01-18

Analytic functions are well worth getting to know - they are very powerful and you will find that they will repay you many times for any effort you put into learning them. Run the inner query on its own - experiment, it's how I learnt. BTW, it's always worth tagging your question with the version of PostgreSQL that you are using!

A more traditional method of doing this would be

SELECT x, y, mc FROM
(
  SELECT col1 AS x, col2 AS y, MAX(col3) AS mc
  FROM test
  GROUP BY col1, col2
) AS tab
ORDER BY mc

Same result - also on the fiddle.

| improve this answer | |
  • 1
    That's it! I've read some queries with the ROW_NUMBER() OVER... but didn't know about it. I'm going to learn about the Analytic Functions. Thank you. – Luis May 5 at 22:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.