The difference between rank () over, Dense_rank (), Row_number () in Oracle

Excerpt from: http://www.linuxidc.com/Linux/2015-04/116349.htm

The difference between rank () over, Dense_rank (), Row_number () in Oracle

Suppose there is a student table student, the student table has a name, a score, a course number, and now I need to sort the students ' grades according to the course.

SELECT * FROM Student

1. Rank over () can be achieved on the student rankings, characterized by the same results as two are tied, as follows 1 2 2 4 5

Select name,       course,       throughorderbydesc as  Rank  from student;

2. Dense_rank () and rank over () are similar, but the students ' grades will not be vacated by the rank of the tie, as follows 1 2 2 3 4

Select name,       course,       throughorderbydesc as  Rank  from student;

3. Row_number This function does not need to consider whether or not to tie, that is afraid to query the same value according to the same number will be continuously ranked

Select name,       course,       throughorderbydesc as  Rank  from student;

Answering:

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

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

Partition by simply rank the original data (the number of records is the same)

Group BY is an aggregated statistic of the raw data (the number of records may be less, one for each group)

3. When you use rank over (), the null value is the largest, and if the sort field is null, it may cause null fields to be ranked first, affecting the sorting result.

Can do this:

 Over  by Order  by desc nulls last)

See Oracle Feature page for more information on Oracle http://www.linuxidc.com/topicnews.aspx?tid=12

This article permanently updates the link address : http://www.linuxidc.com/Linux/2015-04/116349.htm

The difference between rank () over, Dense_rank (), Row_number () in Oracle