CODEWITHSUNDEEP
mysql
Digital Pros
Digital Pros

Reputation: 39

Mysql order by and group by for a feed table

I am trying to do a group by and order by in mysql but am not getting the results I need. Here is my table structure Table name is feed id, date, user_id, item_id, item_type It is a table for a feed of activity on a site. I want the main feed to have the latest item that each user has posted but limit to only the latest post not all of their posts.

Here is the simple mysql query that I tried

select * from feed group by user_id order by date desc

I only get one post from each user but it is not their latest post and it's not in order from the newest to oldest.

Upvotes: 0

Views: 82

Answers (1)

Thomas Chan
Thomas Chan

Reputation: 1

Assuming the latest post has the max value of feed_id, the following query should give you what you need:

SELECT f.* 
FROM feed f 
JOIN (SELECT user_id, max(feed_id) as feed_id FROM feed group by user_id) g 
on f.feed_id = g.feed_id

Upvotes: 0

Related Questions