'How to get the last transaction of each player - SQL Solution

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?

mysqlsql


Solution 1:[1]

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

Solution 2:[2]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 amirhosein hadi
Solution 2 Salman A

Related Questions