SQL statement used to obtain the first N records and generate an automatic numeric sequence for MySQL grouping and sorting

-- MySQL group sorting is the simplest SQL statement for the first N records.


USE test;


Drop table if exists test;


Create table test (
Id int primary key,
Cid INT,
Author VARCHAR (30)
) ENGINE = MYISAM;


Insert into test VALUES
(1, 1, 'test1 '),
(2, 1, 'test1 '),
(3,1, 'test2 '),
(4,1, 'test2 '),
(5, 1, 'test2 '),
(6, 1, 'test3 '),
(7, 1, 'test3 '),
(8, 1, 'test3 '),
(9,1, 'test3 '),
(10, 2, 'test11 '),
(11,2, 'test11 '),
(12, 2, 'test22 '),
(13,2, 'test22 '),
(14,2, 'test22 '),
(15,2, 'test33 '),
(16,2, 'test33 '),
(17,2, 'test33 '),
(18,2, 'test33 ');


Insert into test VALUES (200,200, '200test _ nagios ');




SELECT * FROM (SELECT cid, author, COUNT (*) AS number FROM test group by cid, author)
WHERE
N> (
Select count (*)
FROM (SELECT cid, author, COUNT (*) AS number FROM test group by cid, author) B
WHERE a. cid = B. cid AND a. number <B. number
) Order by cid, number DESC;

The result is as follows:

mysql> SELECT * FROM (SELECT cid,author,COUNT(*) AS number FROM test GROUP BY cid,author) a     -> WHERE      -> 3>(    ->     SELECT COUNT(*)     ->     FROM (SELECT cid,author,COUNT(*) AS number FROM test GROUP BY cid,author) b    ->     WHERE a.cid=b.cid AND a.number<b.number    -> )ORDER BY cid,number DESC;+------+----------------+--------+| cid  | author         | number |+------+----------------+--------+|    1 | test3          |      4 ||    1 | test2          |      3 ||    1 | test1          |      2 ||    2 | test33         |      4 ||    2 | test22         |      3 ||    2 | test11         |      2 ||  200 | 200test_nagios |      1 |+------+----------------+--------+7 rows in set (0.00 sec)

N is the first judgment after the grouping, and N = 3 is the first three.

-- Generate automatic numeric sequence
SET @ ROW = 0;
SELECT a. *, (@ ROW: = @ ROW + 1) Rank
FROM test;