Group data filtering (obtain the nth data in each group after group)

Today, someone in a group asked a question: how to execute a query in msql: retrieve data from a table, group data by a field, and then retrieve the third data in each group. Some people talk about the implementation of Oracle and use rank. Of course, I don't understand oracle. Google's result is that there is no rank function in MySQL. Then I found an articleArticleAfter studying it, I finally made a hard move. Below is a link to the reference article: http://blog.sina.com.cn/s/blog_4d18beb10100y3kt.html

Create a table as an example:

 1   Create   Table  Sam (  2 'A' Int ( 11 ) Default   Null  , 3 'B' Int ( 11 ) Default   Null  4 ) Engine = MyISAM Default Charset =  Utf8;  5   Insert   Into Sam Values (1 , 10 ),( 1 , 15 ),( 1 , 20 ),( 1 , 25 ),( 2 , 20 ),( 2 ,22 ),( 2 , 33 ),( 2 , 45 );

The data in the table is as follows:
A B
110
115
120
125
220
222
233
245

Then there is a query that adds the serial number to each group of rows:

 1   Select  A, B,  2       @ Rank : = If ( @  = A, @ Rank  +  1 , 1  ) Rank,  3       @ : =  A  4       From  'Sam ',  5 (Select   @ Rank : =  1 , @ : =  Null ) Tt;

Query results:

A B rank @ A: =
11011
11521
12031
12541
22012
22222
23332
24542

There are some points to explain here.
Row 1st: User variables and if functions are used here. User variables are temporary variables for each query, similar to the temporary variables in the Java method. The IF function is similar to the IF statement, which contains three parameters. The first is the condition, the second is the return value when the condition is successful, and the third is the return value when the condition fails.
Row 3rd: Save the value of column A to compare the value of column A with the value of column A in the IF column to determine whether rank needs to be reset to 1.
In row 3, nothing is actually done here. The main function is similar to declaring variables. It will be very strange to not declaring the results anyway. The detailed reasons have not been studied clearly.

To sum up, the query statement for the third row in each group is:

 1   Select T. A, T. B From ( Select A, B, @ Rank : =  If ( @  = A, @ Rank  +  1 , 1 ) Rank,@ : = A From  Sam) T,  2 ( Select   @ Rank : =  1 , @ : =  Null ) TT Where Rank =  3 ;

Query results:

A B
120
233

The sequence is not considered above, because the raw data is originally ordered. If you consider the order of A and B and the possible duplicate values of B, you can use the following statement to insert several duplicate data entries for testing:

 Select T. A, T. B From ( Select  F. A, F. B,  @ Rank : =  If ( @ B  <> F. B, If ( @ = F., @ Rank  +  1 , 1 ), @ Rank  ) Rank,  @ : =  F.,  @ B : =  F. B  From ( Select A, BFrom 'Sam' Order   By  A, B) F ,(  Select   @ Rank : =  1 , @ : =  Null , @ B : =  Null ) TT) T Where Rank=  3 ;