How to get the last transaction of each player

I have a database where I store all transactions made in a game. (Picture of DB) Now I want to get the value of the last transaction for each player.

I already tried SELECT MAX(timestamp), value, account_id FROM transactions GROUP BY account_id;. Here I get the right time and account_id, but not the right value.

Is it possible to solve this problem only with SQL?

CodePudding user response：

In MySQL 8 or later you can use window functions for this:

with cte as (
    select *, row_number() over (partition by account_id order by timestamp desc) as rn
    from transactions
)
select *
from cte
where rn = 1

CodePudding user response：

select * from table 
where id in (select MAX(id) from table group by account_id)