CODEWITHSUNDEEP
sqlpostgresqldatetimesql-order-bygreatest-n-per-group
Yogus
Yogus

Reputation: 2272

Fetch latest date records with group by

I am trying to fetch the records with latest records with unique report_ids (col_1_0_ ). Possible group by col_1_0_ and fetch the latest of that group record using this column col_10_0_ timestamp.

I tried to search this forum to find the way but it did not worked for me . It will be helpful if anyone help me to find the similar thread or help to make to get the results.

col_1_0_|ppi_event_id|col_2_0_                  |col_10_0_              |
--------|------------|--------------------------|-----------------------|
  149056|        3249|Draft                     |2020-08-25 13:01:49.016|
  149056|        3249|Submitted                 | 2020-08-25 13:10:22.01|
  149056|        3249|Submitted to administrator|2020-08-25 13:12:39.367|
  149056|        3249|Validated                 |2020-08-25 13:13:28.879|
  149060|        3249|Submitted to administrator|2020-08-25 13:32:41.924|

The expected result is

col_1_0_|ppi_event_id|col_2_0_                  |col_10_0_              |
--------|------------|--------------------------|-----------------------|
  149056|        3249|Validated                 |2020-08-25 13:13:28.879|
  149060|        3249|Submitted to administrator|2020-08-25 13:32:41.924|

Anyone help in this regard. Update : I have tried the solution mentioned below but sometimes it shows the first record "Draft" rather than "Validated" Any other option to try ?

Upvotes: 0

Views: 62

Answers (2)

Atif
Atif

Reputation: 2210

Traditional SQL format is to use row_number()

Demo

SELECT * FROM (
SELECT A.*, 
  ROW_NUMBER() OVER( PARTITION BY col_1_0_ ORDER BY col_10_0_) AS RN
FROM TABLE1 A ) X WHERE RN = 1;

Upvotes: 0

GMB
GMB

Reputation: 222472

In Postgres, I would recommend using distinct on: that's a nice extension to the SQL standard, that was built exactly for the purpose you describe.

select distinct on (col_1_0) t.*
from mytable t
order by col_1_0, col_10_0_ desc

Upvotes: 2

Related Questions