0

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?

justastefan
  • 595
  • 5
  • 18
  • What have you tried so far? Can you show us a sample data with your table schema and expected results based on that? – bonCodigo Jan 27 '13 at 11:53
  • You should tag your question with greatest-n-per-group, and also search for that tag for similar questions. I think that may point you in the right direction. If it were me, I would probably just do multiple queries in this case. Like bonCodigo said, it would help to see what you have so far too. – kstevens715 Jan 27 '13 at 13:34

2 Answers2

1

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

Laxmi Kadariya
  • 1,103
  • 1
  • 14
  • 34
0

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?

Community
  • 1
  • 1
kstevens715
  • 760
  • 5
  • 20