SQL Query on Postgres v12, to get only last record of every duplicate

Job ID: 33910323

Budget: €30 – €250 EUR

I am using Postgres v12, and I have a table with duplicated rows.

This table, called "notifications" has the following columns:

id (unique)
request_id
created_at

I I want not only to find duplicates, but to retrieve only the last entry for each duplicate. For instance:

| id | request_id | created_at |
|---- |------------ |------------ |
| 1 | a | 2020.06.06 |
| 2 | a | 2020.05.05 |
| 3 | b | 2020.04.04 |
| 4 | b | 2020.03.03 |
| 5 | c | 2020.04.04 |
| 6 | c | 2020.03.03 |

Should retrieve rows with id 1,3,5 , since they are the last entry of each duplicate.

I need to use the result of this function to query other table called "events" which has the following fields:

id (unique)
notification_id (the id of the table notification)
event

For instance:

| id | notification_id | event |
|---- |----------------- |-------- |
| 10 | 1 | done |
| 11 | 2 | done |
| 12 | 3 | undone |
| 13 | 4 | done |
| 14 | 5 | done |
| 15. | 6 | done |

The query should only retrieve row with ID 10 and 14, since:
- id 10 has notification_id = 1, which is the last entry of a duplicate and has event = done.
- id 14 has notification_id = 5 which is the last entry of a duplicate and has event = done.
The others are not returned because the notification_id is not the last entry of a duplicate or has event undone.

So, to sum up, I need a query which returns the rows of the table events which:
1) The notification_id is the last entry of a duplicate on the table "notification"
2) The event is = "done"

Please. Let me know what is your fixed price for this. Test the query yourself before submitting it. I will pay inmediately if it works.
Related categories: SQL Database Administration PostgreSQL