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.

2 answers

  • answered 2018-01-16 23:46 Gordon Linoff

    In Postgres, this is easiest using distinct on:

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

  • answered 2018-01-17 08:41 Newbie92

    Or you can do it like this

        SELECT *
        FROM (SELECT *
        , row_number() over(PARTITION BY user_id ORDER BY id DESC) AS RN
        FROM table1) AS A 
        WHERE A.user_id IN (10, 11, 12)
        AND A.RN = 1
        ORDER BY A.id;