CODEWITHSUNDEEP
mysqlsql
Scamander 1920
Scamander 1920

Reputation: 7

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?

Upvotes: 0

Views: 1127

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272386

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

Upvotes: 2

amirhosein hadi
amirhosein hadi

Reputation: 436

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

Upvotes: 0

Related Questions