take last row of multiple transactions for distinct users

I have a table which has a structure like this:

id
user_id
type
previous_amount
amount
current_amount

some of the records might look like this

id, user_id, type, last_amount, amount, cur_amount
1,    10,     CR,     20.0,     10.0,    30.0
2,    11,     CR,     40.0,     15.0,    55.0
3,    10,     DR,     30.0,     5.0,     25.0
4,    12,     CR,     5.0,      20.0,    25.0
5,    11,     DR,     55.0,     10.0,    45.0

I want the result to look like this:

3,    10,     DR,     30.0,     5.0,     25.0
4,    12,     CR,     5.0,      20.0,    25.0
5,    11,     DR,     55.0,     10.0,    45.0

The last transaction of user 10, 11, 12. The number of rows should be equal to the number of users in the table. And the result should be the last transaction that user made. In this case, user 10 had a DR, user 11 had a DR, and user 12 had a CR.

how can I write this in PostreSQL? It would be even better if it is in JPA repository form.

Answer

In Postgres, this is easiest using distinct on:

select distinct on (user_id) t.*
from t
order by user_id, id desc;

Advertisement

Answer