Differences between rank () over, dense_rank (), and row_number () in Oracle

Source: Internet
Author: User
Differences between rank () over, dense_rank (), and row_number () in Oracle

Differences between rank () over, dense_rank (), and row_number () in Oracle

Differences between rank () over, dense_rank (), and row_number () in Oracle

Suppose there is a student table student, which contains the name, score, and course number. Now I need to sort the students' scores according to the course.

Select * from student

1. rank over () is used to rank students with the same scores, as shown in the following figure: 1 2 2 4 5

Select name,
Course,
Rank () over (partition by course order by score desc) as rank
From student;

2. dense_rank () and rank over () are very similar, but after the students' scores are tied together, the rankings are not empty, as shown in the following 1 2 2 3 4

Select name,
Course,
Dense_rank () over (partition by course order by score desc) as rank
From student;

3. The row_number function does not need to consider whether it is collocated or not. It is afraid that continuous ranking will be performed even if the value queried by the condition is the same.

Select name,
Course,
Row_number () over (partition by course order by score desc) as rank
From student;


Q &:

1. partition by is used to partition the result set.

2. What is the difference between partition by and group?

Partition by only sorts the original data (the number of records remains unchanged)


Group by refers to aggregation statistics on raw data (the number of records may be smaller, and one record is returned for each group)

3. When rank over () is used, the null value is the largest. If the sorting field is null, the null field may be placed at the top, affecting the sorting result.

It can be like this: rank over (partition by course order by score desc nulls last)

This article permanently updates the link address:

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.