CODEWITHSUNDEEP
mysql
space_food_
space_food_

Reputation: 830

Why does this order by then group by not work?

Let's say I have MySQL tables like:

Users (id)
Posts (id, user_id, created)

Then let's say I want to try to get the most recent post for each user

SELECT * FROM(SELECT * FROM posts ORDER BY created DESC) AS b GROUP BY user_id

It is not correctly returning the most recent post, however, it does when not grouping. What is the proper way to perform such a query?

Upvotes: 0

Views: 33

Answers (1)

Aleks G
Aleks G

Reputation: 57316

You seem to be overthinking it - think eaiser.

Assuming that your post IDs are sequential, then the largest ID corresponds to the latest post - and you can get your data like this:

select user_id, id, created from Posts where id in
(select max(id) from posts group by user_id)

Upvotes: 3

Related Questions