MySQL uses custom sequences to implement row_number functions (detailed steps)

After reading some articles, I finally figured out how to implement row_number() sorting in MySQL.

Without further ado, let me show you the code:

Step 1: Create a table:

create table grades(
`name` varchar(10),
`subject` varchar(10),
`score` int(10)
)

Step 2: Write data

insert into grades(name, subject, score)
values('Xiao Ming', 'Chinese', 85),
('Xiaohua', 'Chinese', 89),
('Xiao Li', 'Chinese', 91),
('Xiaofang', 'Chinese', 93),
('Xiao Ming', 'Mathematics', 77),
('Xiaohua', 'Mathematics', 95),
('Xiao Li', 'Mathematics', 83),
('Xiaofang', 'Mathematics', 88),
('Xiao Ming', 'English', 90),
('Xiaohua', 'English', 92),
('Xiao Li', 'English', 85),
('Xiaofang', 'English', 88)

The data is as follows:

Step 3:
Requirement: Find the second student in each subject

First, sort:

select name, subject, score 
from grades
order by subject, score desc

The data is as follows:

Then, each subject is sorted by group

select (@i:=case when @subject_pre=t1.subject then @i+1 else 1 end) as rn,
t1.*,
(@subject_pre:=subject) 
from (
    select name, subject, score 
    from grades
    order by subject, score desc
) t1, 
(select @i:=0, @subject_pre:='') as t2
group by subject, score
order by subject, score desc 

To explain:
Add a comparison item subject_pre to record what the previous subject was.
Add a self-increasing sequence to implement the index+1 function.
Because the data is already in order, if the subject pointed to is the same as the previous subject stored, the sequence number is increased by 1, otherwise the sequence number is recalculated from 1.
This achieves group sorting.

Finally, take out the data of rn=2

select name, subject, score from(
select (@i:=case when @subject_pre=t1.subject then @i+1 else 1 end) as rn,
t1.name,
t1.subject,
t1.score,
(@subject_pre:=subject) 
from (
select name, subject, score 
from grades
order by subject, score desc
) t1, 
(select @i:=0, @subject_pre:='') as t2
group by subject, score
order by subject, score desc
)
where rn=2

The final result is as follows:

In this way, the row_number() function is implemented using mysql.

A lot of the information I found on the Internet is not clearly written, so here I use an example to explain this implementation clearly. I hope it will be helpful to you!

This is the end of this article about how to use custom sequences in MySQL to implement the row_number function. For more information about the MySQL row_number function, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!