CODEWITHSUNDEEP
mysqlsql
justastefan
justastefan

Reputation: 595

How to retrieve the latest posts of multiple users using one or two SQL statements?

I am having a table in an mysql database like this:

Postings (id, content, user_id, create_date)

Now, for ten user (user_id) i want to retrieve his five latest postings (id). A simple approach would be to figure the 10 users with the latest posts in one query, and then fire 10 separat queries for getting the latest 5 posts for each user. Is there a more simple way by putting it in one or two queries?

Upvotes: 0

Views: 226

Answers (2)

Laxmi Kadariya
Laxmi Kadariya

Reputation: 1103

use this

SELECT * FROM (SELECT * FROM TABLE ORDER BY ID DESC LIMIT 5) AS ttbl ORDER BY ID ASC;

here the code inside the bracket displays the latest 5 posting in reverse order. so that data is first stored in ttbl and again order in the appropriate order to get the desired result.

Hope it might solve you

Upvotes: 1

kstevens715
kstevens715

Reputation: 760

Here is one possible solution off the top of my head (untested). This returns the ids for each user in a single column. It's not going to be the fastest query.

This just returns the id's, not the post itself. Honestly, I think you should just go with your original idea of doing multiple queries.

Select 
(Select group_concat(id order by id desc)
From postings g
Where g.user_id=s.user_id
Limit 5) ids
From  (
Select user_id
From postings p
Group by user_id
Order by id desc
Limit 10) s

Also, check out this answer to a similar question: How to SELECT the newest four items per category?

Upvotes: 0

Related Questions