[MySQL] First group by a field and then take the first N records in each set

From:http://blog.chinaunix.net/uid-26729093-id-4294287.html

Please refer to: http://bbs.csdn.net/topics/330021260

CREATE TABLE T2 (
ID int PRIMARY KEY,
GID Char,
col1 int,
col2 int
) Engine=myisam;

INSERT INTO T2 values
(1, ' A ', 31,6),
(2, ' B ', 25,83),
(3, ' C ', 76,21),
(4, ' D ', 63,56),
(5, ' E ', 3,17),
(6, ' A ', 29,97),
(7, ' B ', 88,63),
(8, ' C ', 16,22),
(9, ' D ', 25,43),
(Ten, ' E ', 45,28),
(One, ' A ', 2,78),
(30,79, ' B ',
(The ' C ', 96,73),
(+, ' D ', 37,40),
(d, ' E ', 14,86),
(+, ' A ', 32,67),
(+, ' B ', 84,38),
(' C ', 27,9),
(+, ' D ', 31,21),
(c, ' E ', 80,63),
(+, ' A ', 89,9),
(' B ', 15,22),
(at all, ' C ', 46,84),
(' D ', 54,79),
(+, ' E ', 85,64),
(+, ' A ', 87,13),
(' B ', 40,45),
(34,90, ' C ',
(' D ', 63,8),
(+, ' E ', 66,40),
(83,49, ' A ',
(+, ' B ', 4,90),
(81,7, ' C ',
(11,12, ' D ',
(E ', 85,10),
(39,75, ' A ',
(Panax Notoginseng, ' B ', 22,39),
(+, ' C ', 76,67),
(20,11, ' D ',
(+, ' E ', 81,36);


Expected results
1) N=1 take GID COL2 Maximum records per group
+----+------+------+------+
| ID | GID | col1 | col2 |
+----+------+------+------+
| 6 |   A |   29 | 97 |
| 15 |   E |   14 | 86 |
| 24 |   D |   54 | 79 |
| 28 |   C |   34 | 90 |
| 32 |    B |   4 | 90 |
+----+------+------+------+
2) N=3 take GID COL2 maximum of 3 records per group
+----+------+------+------+
| ID | GID | col1 | col2 |
+----+------+------+------+
| 6 |   A |   29 | 97 |
| 11 |    A |   2 | 78 |
| 36 |   A |   39 | 75 |
| 32 |    B |   4 | 90 |
| 2 |   B |   25 | 83 |
| 12 |   B |   30 | 79 |
| 28 |   C |   34 | 90 |
| 23 |   C |   46 | 84 |
| 13 |   C |   96 | 73 |
| 24 |   D |   54 | 79 |
| 4 |   D |   63 | 56 |
| 9 |   D |   25 | 43 |
| 15 |   E |   14 | 86 |
| 25 |   E |   85 | 64 |
| 20 |   E |   80 | 63 |
+----+------+------+------+


SELECT A.id,a.gid,a.col1,a.col2 from T2 a
Left JOIN T2 b
On A.gid=b.gid and A.col2<=b.col2
GROUP by A.id,a.gid,a.col1,a.col2
Having COUNT (b.id) <=3
ORDER by a.gid,a.col2 Desc


SELECT A.id,a.gid,a.col1,a.col2 from T2 a
WHERE 3>= (
SELECT COUNT (*) from T2 b
WHERE A.gid=b.gid and A.col2<=b.col2)
ORDER by a.gid,a.col2 Desc

[MySQL] First group by a field and then take the first N records in each set