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