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

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: