Limit result from mysql query per column rows Count

Question

First, I have a table schema look like this:

CREATE TABLE `list` (`id` INT NOT NULL AUTO_INCREMENT , `parentId` INT NOT NULL , PRIMARY KEY (`id`));

INSERT INTO `list` (`id`, `parentId`) VALUES ('1', '1');
INSERT INTO `list` (`id`, `parentId`) VALUES ('2', '1');
INSERT INTO `list` (`id`, `parentId`) VALUES ('3', '1');

INSERT INTO `list` (`id`, `parentId`) VALUES ('4', '2');
INSERT INTO `list` (`id`, `parentId`) VALUES ('5', '2');
INSERT INTO `list` (`id`, `parentId`) VALUES ('6', '2');

INSERT INTO `list` (`id`, `parentId`) VALUES ('7', '3');
INSERT INTO `list` (`id`, `parentId`) VALUES ('8', '3');
INSERT INTO `list` (`id`, `parentId`) VALUES ('9', '3');

I have a query which return this data for example:

| id | parentId |
|----|----------|
|  1 |        1 |
|  2 |        1 |
|  3 |        1 |
|  4 |        2 |
|  5 |        2 |
|  6 |        2 |
|  7 |        3 |
|  8 |        3 |
|  9 |        3 |

All the data above generated from select query:

SELECT `id`, `parentId` FROM `table` ORDER BY `id`;

I need the result to be max 2 records per parentId. The result is need be look like this:

    | id | parentId |
    |----|----------|
    |  1 |        1 |
    |  2 |        1 |
    |  4 |        2 |
    |  5 |        2 |
    |  7 |        3 |
    |  8 |        3 |

How do I achieve this? I've been searching around on the net but seems like getting no luck. Any help will be much appreciated.

Thank you.

Answer 1

For MySql 8.0+ you can use ROW_NUMBER():

SELECT t.id, t.parentid
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY parentid ORDER BY id) rn
  FROM list
) t
WHERE t.rn <= 2

See the demo.
Results:

| id  | parentid |
| --- | -------- |
| 1   | 1        |
| 2   | 1        |
| 4   | 2        |
| 5   | 2        |
| 7   | 3        |
| 8   | 3        |

For previous versions where you can't use window functions, try this (not so efficient for large tables) solution:

SELECT t.* 
FROM list t
WHERE 2 > (
  SELECT COUNT(*) 
  FROM list 
  WHERE parentid = t.parentid AND id < t.id
)

See the demo.