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

SQL statement for MySQL grouping and sorting based on the first N records and automatic numeric sequence generation-the simplest single SQL statement for MySQL grouping and sorting based on the first N records. USEtest; DROPTABLEIFEXISTStest; CREATETABLEtest (idINTPRIMARYKEY, cidINT, authorVARCHAR (30) ENGINEMYISAM; INSERTINTOtestV

SQL statement for MySQL grouping and sorting based on the first N records and automatic numeric sequence generation-the simplest single SQL statement for MySQL grouping and sorting based on 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 V

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 ) 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
 
   )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;