CODEWITHSUNDEEP
sqlpostgresqlgroup-bydistinct-on
EnzoR
EnzoR

Reputation: 3346

How to replace a DISTINCT ON with GROUP BY in PostgreSQL 9?

I have been using the DISTINCT ON predicate and have decided to replace it with GROUP BY, mainly because it "is not part of the SQL standard and is sometimes considered bad style because of the potentially indeterminate nature of its results".

I am using DISTINCT ON in conjunction with ORDER BY in order to select the latest records in a history table, but it's not clear to me how to do the same with the GROUP BY. What could be a general approach in order to move from one construct to the other one?

An example could be

SELECT
  DISTINCT ON (f1, f2 ) *
  FROM table
  ORDER BY f1, f2, datefield DESC;

where I get the "latest" pairs of (f1,f2).

Upvotes: 3

Views: 3105

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

If you have a query like this:

select distinct on (col1) t.*
from table t
order by col1, col2

Then you would replace this with window functions, not a group by:

select t.*
from (select t.*,
             row_number() over (partition by col1 order by col2) as seqnum
      from table t
     ) t
where seqnum = 1;

Upvotes: 3

Related Questions