SQL Select most recent record for each group

Question

I am trying to get the most recent record for each user in my table:

SELECT *  
FROM Orders 
WHERE State = Active 
GROUP BY UserId
ORDER BY Orders.DateTimePlanned DESC`

But this results me in the oldest record of each user, how can I get the most recent one!? Changing the DESC to ASC doesn't work!

Please let me know!

Answer 1

Your code is not valid standard SQL. Presumably, you are running MySQL with sql mode ONLY_FULL_GROUP_BY disabled.

You need to filter the dataset rather than aggregate it. One option uses a subquery:

select *  
from orders o
where state = 'Active' and datetimeplanned = (
    select max(o1.datetimeplanned)
    from orders o1
    where o1.userid = o.userid and o1.state = 'Active'
)

You can also use window functions (available in MySQL 8.0 only):

select *  
from (
    select o.*, rank() over(partition by userid order by datetimeplanned desc) rn
    from orders o
    where state = 'Active'
) o
where rn = 1