How to get last value for each user_id (postgreSQL)

Question

Current ratio of user is his last inserted ratio in table "Ratio History"

user_id | year | month | ratio

For example if user with ID 1 has two rows

1 | 2019 | 2 | 10
1 | 2019 | 3 | 15

his ratio is 15.

there is some slice from develop table

user_id | year | month | ratio
1 | 2018 | 7 | 10
2 | 2018 | 8 | 20
3 | 2018 | 8 | 30
1 | 2019 | 1 | 40
2 | 2019 | 2 | 50
3 | 2018 | 10 | 60
2 | 2019 | 3 | 70

I need a query which will select grouped rows by user_id and their last ratio.

As a result of the request, the following entries should be selected

user_id | year | month | ratio
    1 | 2019 | 1 | 40
    2 | 2019 | 3 | 70
    3 | 2018 | 10 | 60

I tried use this query

select rh1.user_id, ratio, rh1.year, rh1.month from ratio_history rh1
join (
    select user_id, max(year) as maxYear, max(month) as maxMonth
    from ratio_history group by user_id
    ) rh2 on rh1.user_id = rh2.user_id and rh1.year = rh2.maxYear and rh1.month = rh2.maxMonth

but i got only one row

Gordon Linoff Gordon Linoff · Accepted Answer · 2019-08-29T12:10:25

Use distinct on:

select distinct on (user_id) rh.*
from ratio_history rh
order by user_id, year desc, month desc;

distinct on is a very convenient Postgres extension. It returns one row for the key values in parentheses? Which row, it is the first row based on the sort criteria. Note that the sort criteria need to start with the expressions in parentheses.

How to get last value for each user_id (postgreSQL)

1 Answers