The difference between row_number (), rank (), Dense_rank () in Oracle ____oracle

Source: Internet
Author: User

The use of Row_numbeR is very extensive, sorting is best used, it will generate an ordinal number for each row of the query, sorted and will not repeat, note that when using the Row_number function, you must choose to sort a column with the over clause to generate the ordinal number.

The Rank function returns the rank of each row in the partition of the result set, and the row is ranked before the related row plus one. In simple terms, the rank function is the ranking of the records of the query, unlike the Row_number function, the rank function takes into account the same sort field values in the over clause, if the rank function is used to generate the ordinal number, the same ordinal number is the same as the value of the sorted field in the over clause. Sequential numbers that are not the same in the following fields will skip the same rank number next, that is, the number of rankings before the related row plus one, which can be understood to generate an ordinal based on the current number of records, and so on.

The function of the Dense_rank function is similar to the Rank function, where the Dense_rank function is contiguous when the ordinal is generated, and the ordinal number generated by the rank function may be discontinuous. When the Dense_rank function appears in the same rank, the same rank number is not skipped, and the rank value is immediately the last rank. In each grouping, rank () is a hop sort, with two first names followed by a fourth, Dense_rank () is sequential, with two first names still followed by second.

With examples, you can understand more intuitively:

Suppose there is now a student table student, and the student table has a name, a score, and a course number.

SELECT * from student;

Now you need to sort the students ' grades according to the course:

--row_number () Sort
Select Name,course,row_number () over (partition by course orders by score desc) rank from S Tudent;

--rank () Jump sort, if there are two first levels, Next is the third level
select Name,course,rank () over (partition by course orders by score Desc) Rank from student;

--dense_rank () sequential sorting, if there are two first levels, next is the second level 
select Name,course,dense_rank () over (partition by course order By score Desc) rank from student;

get the first place in each course:

--The first name of each course is one: 
select * FROM (select Name,course,row_number () over (partition by course ORDER BY score Desc) r Ank from student) where rank=1;
--The first of each course takes all: 
select * FROM (select Name,course,dense_rank () over (partition by course ORDER BY score Desc) RA NK from student) where rank=1;
--Each course first takes all:
select * FROM (select Name,course,rank () over (partition by course ORDER BY score desc) Rank fro M student) where rank=1;

Attach: First in each course take all other methods (using group by instead of partition by):

s.* from student S
inner JOIN (select Course,max (score) as score to student group by course) C
o n S.course=c.course and S.score=c.score;
--or simplify the connection
using the Using keyword * from student S
inner JOIN (select Course,max (score) as score to student group by course) C
 Using(Course,score);  

about Parttion by:

The parttion by keyword is part of the analytic function in Oracle that is used to partition the result set. It differs from the aggregate function group by that it simply places the original data in a ranking, can return more than one record in a group (the number of records is unchanged), and group by is the aggregation of the original data statistics, generally only one reflects the results of statistics (each group returns one).

TIPS:

When you use rank over (), the null value is the largest, and if the sort field is null, it can cause null fields to be in the front, affecting the sort results.

You can do this: rank over (partition by course ORDER BY score Desc nulls last)

Summary:

The following three points need to be noted when using the ranking function:

1, the ranking function must have over clause. Row_number is very versatile, sorting is best used, it will generate an ordinal number for each row of the query, sorted and will not repeat, note that when using the Row_number function, you must select a column to be sorted with the over clause to generate the ordinal number.


The Rank function returns the rank of each row in the partition of the result set, and the row is ranked before the related row plus one. In simple terms, the rank function is the ranking of the records of the query, unlike the Row_number function, the rank function takes into account the same sort field values in the over clause, if the rank function is used to generate the ordinal number, the same ordinal number is the same as the value of the sorted field in the over clause. Sequential numbers that are not the same in the following fields will skip the same rank number next, that is, the number of rankings before the related row plus one, which can be understood to generate an ordinal based on the current number of records, and so on.


The function of the Dense_rank function is similar to the Rank function, where the Dense_rank function is contiguous when the ordinal is generated, and the ordinal number generated by the rank function may be discontinuous. When the Dense_rank function appears in the same rank, the same rank number is not skipped, and the rank value is immediately the last rank. In each grouping, rank () is a hop sort, with two first names followed by a fourth, Dense_rank () is sequential, with two first names still followed by second.


With examples, you can understand more intuitively:


Suppose there is now a student table student, and the student table has a name, a score, and a course number.


SELECT * from student;




Now you need to sort the students ' grades according to the course:


--row_number () Sequential sort
Select Name,course,row_number () over (partition by course ORDER BY score desc) rank from student;




--rank () Jump sort, if there are two first levels, then the third level
Select Name,course,rank () over (partition by course ORDER BY score desc) rank from student;




--dense_rank () sequential sort, if there are two first levels, then the second level
Select Name,course,dense_rank () over (partition by course ORDER BY score desc) rank from student;




Get the first place in each course:


--The first name of each course is only one:
SELECT * FROM (select Name,course,row_number ()-Partition by course order BY score desc) rank from student) where rank = 1;
--The first of each course takes all:
SELECT * FROM (select Name,course,dense_rank ()-Partition by course order BY score desc) rank from student) where rank = 1;
--The first of each course takes all:
SELECT * FROM (select Name,course,rank ()-Partition by course order BY score desc) rank from student) where rank=1;
Attach: First in each course take all other methods (using group by instead of partition by):


Copy Code
Select s.* from student s
INNER JOIN (select Course,max (score) as score from student group by course) c
On S.course=c.course and S.score=c.score;
--or simplify the connection using the Using keyword
SELECT * FROM student S
INNER JOIN (select Course,max (score) as score from student group by course) c
using (Course,score);
Copy Code
About Parttion by:


The parttion by keyword is part of the analytic function in Oracle that is used to partition the result set. It differs from the aggregate function group by that it simply places the original data in a ranking, can return more than one record in a group (the number of records is unchanged), and group by is the aggregation of the original data statistics, generally only one reflects the results of statistics (each group returns one).


TIPS:


When you use rank over (), the null value is the largest, and if the sort field is null, it can cause null fields to be in the front, affecting the sort results.


You can do this: rank over (partition by course ORDER BY score Desc nulls last)


Summarize:


The following three points need to be noted when using the ranking function:


1, the ranking function must have over clause.


2. The rank function must have a over clause containing an order by.


3, in the group from 1 to start sorting.

2. The rank function must have a over clause containing an order by.

3, sorted from 1 in group.

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.