Oracle rank function

Source: Internet
Author: User

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;

Related Article

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.