0

Suppose I have a simple table called Articles which has three columns id, category_id, title. I want to select the 3 newest rows for each category_id, thus giving me the latest articles for each category.

Kind of the equivalent of:

SELECT * FROM articles WHERE category_id = 1 ORDER BY id LIMIT 3
UNION
SELECT * FROM articles WHERE category_id = 2 ORDER BY id LIMIT 3
...

I am aware that is somewhat of a duplicate, but many examples over complicate the simple query I'm trying to build and I can't get my head around the various joins, variables etc. needed to make it work.

Just want something simple :)

Many thanks!

Shadow
  • 33,525
  • 10
  • 51
  • 64
John1984
  • 917
  • 2
  • 13
  • 23
  • 1
    I think many of those answers address precisely this problem. But if you're still struggling, see http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Dec 30 '16 at 10:11
  • @Strawberry linked a duplicate of this question – Shadow Dec 30 '16 at 10:15
  • Many of those answers provide a count or max. I'm not looking for an aggregate, but to get the N rows. – John1984 Dec 30 '16 at 10:17
  • @John1984 the linked duplicate is exactly what you are looking for. No counts, no maxes there. – Shadow Dec 30 '16 at 10:21
  • Okay, I've got this (had to take at symbols out): set num := 0; select * from (select *, num := num + 1 as row_number from articles order by category_id) as x where x.row_number <= 3; but it only returns for the first category_id. Any suggestions? – John1984 Dec 30 '16 at 10:25
  • I suggest you to re-read the 2nd solution accepted answer in the duplicate topic and understand it. If you try to modify it without understanding, then you end up in a situation like this. – Shadow Dec 30 '16 at 11:02

0 Answers0