CODEWITHSUNDEEP
postgresqlmaxdistinct
Hajar M
Hajar M

Reputation: 69

How to get distinct results with max of a field

I have a query :

select distinct(donorig_cdn),cerhue_num_rfa,max(cerhue_dt) from t_certif_hue 


 group by donorig_cdn,cerhue_num_rfa 


 order by donorig_cdn

it returns me some repeated ids with different cerhue_num_rfa 2451 2460 2467 are repeated

how do i return only one line for the repeated ids with cerhue_num_rfa that matches the max of date (cerhue_dt) .. and have at the end only 10 results instead of 15 ?

Upvotes: 2

Views: 2505

Answers (1)

Hinni
Hinni

Reputation: 166

Postgres has SELECT DISTINCT ON to the rescue. It only returns the first row found for each value of the given column. So, all you need is an order that ensures the latest entry comes first. No need for grouping.

SELECT DISTINCT ON (donorig_cdn) donorig_cdn,cerhue_num_rfa,cerhue_dt
  FROM t_certif_hue 
  ORDER BY donorig_cdn, cerhue_dt DESC;

Upvotes: 4

Related Questions