CODEWITHSUNDEEP
sqlpostgresql
Viktor
Viktor

Reputation: 1702

How to get last value for each user_id (postgreSQL)

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

Upvotes: 0

Views: 806

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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.

Upvotes: 1

Related Questions