Add additional value to the newest record (but show all records)

Question

I have to create view in MariaDB which contains all data from states table:

| id | user_id | state |

But to the newest record for each user (based on the highest id of state for each user). I have to also add columnis_newest and set it to true (1) - rest of records should get false (0).

Is this possible to do in query?

Answer 1

If you are running MariaDB 10.3 or higher, you can use row_number():

create view states_view as
select 
    id,
    user_id,
    state,
    (row_number() over(partition by user_id order by id desc) = 1) is_newest
from states

In earlier versions, where window functions are not available, one option is a to join an aggregate query:

create view states_view as
select 
    s.id,
    s.user_id,
    s.state,
    (s.id = m.max_id) is_newest
from states s
inner join (select user_id, max(id) max_id from states group by user_id) m
    on m.user_id = s.user_id