Oracle ranking function

Rank () sorts data in a table hierarchically.
For example, student_result
Name number Kemu fenshu
Li 0113101 High 90
Zhang 0113098 high 80
Wang 0113077 High 70
Li 0113101 physical 80
Zhang 0113098 physical 90
Wang 0113077 physical 70

If I want to retrieve the first two names in the table with a high number and the first two names in the physical table
You can use the rank () method to achieve the goal.

-- First sort by Kemu and then sort by fenshu in descending order
Select rank () over (partition by Kemu order by fenshu DESC) rk, T. * From student_result t

Result
Rk name number Kemu fenshu
1 Li 0113101 High 90
2. Zhang 0113098 high: 80
3 Wang 0113077 High 70
1 Zhang 0113098 physical 90
2 Li 0113101 physical 80
3 Wang 0113077 physical 70

-- Then, retrieve the records with rk <= 2, that is, retrieve the first two records.
Select * from (select rank () over (partition by Kemu order by fenshu DESC) rk, T. * From student_result T) as y where Y. rk <= 2;

Result
Rk name number Kemu fenshu
1 Li 0113101 High 90
2. Zhang 0113098 high: 80
1 Zhang 0113098 physical 90
2 Li 0113101 physical 80

The usage of dense_rank () and rank () is identical. The difference is that when the scores are the same
For example
Name number Kemu fenshu
Li 0113101 high 80
Zhang 0113098 high 80
Wang 0113077 High 70
Li 0113101 physical 80
Zhang 0113098 physical 90
Wang 0113077 physical 70

Select rank () over (order by fenshu DESC) rk, T. * From student_result t
The result is
Rk name number Kemu fenshu
1 Zhang 0113098 physical 90
2 Li 0113101 high 80
2. Zhang 0113098 high: 80
2 Li 0113101 physical 80
5 Wang 0113077 High 70
5 Wang 0113077 physical 70
Select dense_rank () over (order by fenshu DESC) rk, T. * From student_result t
Result:
Rk name number Kemu fenshu
1 Zhang 0113098 physical 90
2 Li 0113101 high 80
2. Zhang 0113098 high: 80
2 Li 0113101 physical 80
3 Wang 0113077 High 70
3 Wang 0113077 physical 70

Use of row_number ()

Select row_number () over (order by fenshu DESC) rk, T. * From student_result t
Rk name number Kemu fenshu
1 Zhang 0113098 physical 90
2 Li 0113101 high 80
3 Zhang 0113098 high 80
4 Li 0113101 physical 80
5 Wang 0113077 High 70
6 Wang 0113077 physical 70

Fetch n rows only
The number of rows before the current record is retrieved.

For example
Select row_number () over (order by user_no) from user_files fetch first 5 rows only;