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

Source: Internet
Author: User

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 ;

 


Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.