MySQL rank function implementation _ MySQL

How does the rank function of MySQL implement the rank function of Mysql?-laruence learning Park-BlogJava

Http://www.blogjava.net/dunkbird/archive/2011/01/28/343718.html

Table features:

Mysql> select * from test;

+ ------ +
| A | B |
+ ------ +
| 1 | 20 |
| 1 | 21 |
| 1 | 24 |
| 2 | 20 |
| 2 | 32 |
| 2 | 14 |
+ ------ +
6 rows in set (0.00 sec)

Now, we use Group a to query the maximum two values of column B. How should I write this SQL statement?

1. create a table

Create Table: CREATE TABLE `sam` (  `a` int(11) DEFAULT NULL,  `b` int(11) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8

2. Insert analog data

INSERT INTO `sam` VALUES (1,10),(1,15),(1,20),(1,25),(2,20),(2,22),(2,33),(2,45);

+ ------ +
| A | B |
+ ------ +
| 1 | 10 |
| 1 | 15 |
| 1 | 20 |
| 1 | 25 |
| 2 | 20 |
| 2 | 22 |
| 2 | 33 |
| 2 | 45 |
+ ------ +

3. SQL implementation

select a,b,rownum,rank from     (select ff.a,ff.b,@rownum:=@rownum+1 rownum,if(@pa=ff.a,@rank:=@rank+1,@rank:=1) as rank,@pa:=ff.a     FROM       (select a,b from sam group by a,b order by a asc,b desc) ff,(select @rank:=0,@rownum:=0,@pa=null) tt) result    having rank <=2;

4. results:
+ ------ + -------- + ------ +
| A | B | rownum | rank |
+ ------ + -------- + ------ +
| 1 | 25 | 1 | 1 |
| 1 | 20 | 2 | 2 |
| 2 | 45 | 5 | 1 |
| 2 | 33 | 6 | 2 |
+ ------ + -------- + ------ +
4 rows in set (0.00 sec)

Note:

@ X is a variable,
X: = Y: assign Y to X