Mysql : Get most recent value for specific users

Question

I got a table with three colums : lastUpdate, userId, and userScore

I want to filter that to get only the most recent userScore for 3 userId, and order them by userScore.

So far I tried :

SELECT * 
FROM `mybase` 
WHERE `userId`="120683" 
OR `userId`="206116" 
OR `userId`="259903" 
GROUP BY userId 
ORDER BY userScore DESC

But it's not giving me the most recent entries.

Can you help me?

Thanks

Answer 1

You need to filter rather than aggregate.

Consider:

SELECT b.* 
FROM mybase b
WHERE 
    userId IN (120683, 206116, 259903)
    AND b.lastUpdate = (
        SELECT MAX(b1.lastUpdate) FROM mybase b1 where b1.userId = b.userId
    )
ORDER BY b.userScore DESC

The correlated subquery lets you filter on the latest lastUpdate per userId.

If you are running MySQL 8.0, you can also use window functions for this:

SELECT *
FROM (
    SELECT b.*, ROW_NUMBER() OVER(PARTITION BY userId ORDER BY lastUpdate DESC) rn
    FROM mybase b
    WHERE userId IN (120683, 206116, 259903)
) t
ORDER BY userScore DESC