MySQL - Get x records for every value y in a certain column

Question

I have a table like this:

columns:

id | parent

records:

And for all parents, I want to get the first 2 records, so i should have:

How can I acheive this in MySQL? I need to use subqueries? Thanks.

There is already a question and an answer for this on SO: http://stackoverflow.com/questions/3364224/select-first-n-records-for-each-distinct-id-in-sql-server-2008. --- Sorry that was for MSSQL — Aart Stuurman, Jun 12 '13 at 16:08
well he is using php too isnt he? he can load all parents from database, and after that use array of id to select distinct values. I am on cell phone i might be mistaken (misread, missunderstood), or reverse order. — Kyslik, Jun 12 '13 at 16:12
Search for "[mysql] [greatest-n-per-group]" in SO and you'll find dozens of similar questions. — Barmar, Jun 12 '13 at 16:22
@Kyslik Yes I am using PHP but I prefer this to be done on SQL side. — Tony, Jun 12 '13 at 16:37

score 1 · Answer 1 · answered Jun 12 '13 at 16:19

try this

  SELECT a.id,  a.parent FROM Table1 AS a WHERE 
 (SELECT COUNT(*) FROM Table1 AS b 
 WHERE b.parent = a.parent AND b.id <= a.id) <= 2 
 ORDER BY a.parent ASC, a.id asc

DEMO HERE

 ID     PARENT
 1       1
 2       1
 4       2
 5       2
 7       3
 8       4
 9       5

Bill Karwin · Answer 2 · 2013-06-12T17:07:25.947

Here's a couple of different solutions to try.

SELECT id, parent
FROM (
    SELECT id, (@rownum:=IF(parent=@parent, @rownum+1, 1)) AS rownum, 
      (@parent:=parent) AS parent
    FROM (SELECT @parent := null) AS _init
    STRAIGHT_JOIN MyTable
    ORDER BY parent) AS t
WHERE t.rownum <= 2;

Alternative:

SELECT t1.id, t1.parent
FROM MyTable AS t1
LEFT OUTER JOIN MyTable AS t2
  ON t1.parent = t2.parent AND t1.id >= t2.id
GROUP BY t1.id
HAVING COUNT(*) <= 2;

MySQL - Get x records for every value y in a certain column

2 Answers2