Return paginated list of rows from table where only the latest row is returned filtered by one column and deduplicated on another

Imagine a table of user messages that looks like this:

| ID | MESSAGE | RECIPIENT_ID | ULTIMATE_PARENT_ID | SENT_AT             |
| 1  | Blah.   | 10           | 1                  | 2024-09-10T10:10:00 |
| 2  | Blah2   | 10           | 2                  | 2024-09-11T12:20:00 |
| 3  | Blah3   | 10           | 1                  | 2024-09-12T15:10:00 |
| 4  | Blah4   | 10           | 1                  | 2024-09-13T16:10:00 |

What I would like to send back is the latest message to a user, with the ULTIMATE_PARENT_ID deduplicated. i.e. Each row will have a unique ultimate parent id. I will be sending a pagination object (Pageable) which will hopefully take care of the pagination for me.

Basically the query on the above table should send back:

| ID | MESSAGE | RECIPIENT_ID | ULTIMATE_PARENT_ID | SENT_AT             |
| 2  | Blah2   | 10           | 2                  | 2024-09-11T12:20:00 |
| 4  | Blah4   | 10           | 1                  | 2024-09-13T16:10:00 |

I am using Spring and Spring Data JPA. But if it has to be done using native SQL that is fine. What I have tried, but doesn’t work is:

@Query(value = "SELECT dm FROM " +
        "(SELECT DISTINCT d.ultimateParentId from DirectMessage d WHERE dm.recipient.id = :recipientId) DirectMessage dm " +
        "ORDER BY dm.sentAt DESC", nativeQuery = true)
Page<DirectMessage> findPaginatedDirectMessagesByRecipientIdGroupByUltimateParentId(Long recipientId, Pageable pg);

Any help on this is greatly appreciated because I can’t find a solution, either in my own head or online!

You need to sign in to view this answers