# Detailed Explanation of Oracle Ranking Function of Rank Example

• 2021-11-14 07:24:27
• OfStack

--Known: Two ranking methods (partitioned and unpartitioned): with and without partition

--Two calculation methods (continuous and discontinuous), corresponding functions: dense_rank, rank

Query the original data: student number, name, subject name and grade

select * from t_score

 S_ID S_NAME SUB_NAME SCORE 1 张3 语文 80.00 2 李4 数学 80.00 1 张3 数学 0.00 2 李4 语文 50.00 3 张3丰 语文 10.00 3 张3丰 数学 3 张3丰 体育 120.00 4 杨过 JAVA 90.00 5 mike c++ 80.00 3 张3丰 Oracle 0.00 4 杨过 Oracle 77.00 2 李4 Oracle 77.00

Query each student subject for Oracle ranking (simple ranking) select sc.s_id,sc.s_name,sub_name,sc.score, Ranking of rank () over (order by score desc) from t_score sc where sub_name='Oracle'

 S_ID S_NAME SUB_NAME SCORE 名次 4 杨过 Oracle 77.00 1 2 李4 Oracle 77.00 1 3 张3丰 Oracle 0.00 3

Comparison: rank () vs. dense_rank (): Discontinuous and continuous (both simple) rankings select sc.s_id,sc.s_name,sub_name,sc.score, Ranking of dense_rank () over (order by score desc) from t_score sc where sub_name='Oracle'

 S_ID S_NAME SUB_NAME SCORE 名次 4 杨过 Oracle 77.00 1 2 李4 Oracle 77.00 1 3 张3丰 Oracle 0.00 2

Inquire about the ranking of each student in each subject (regional ranking) select sc.s_id,sc.s_name,sub_name,sc.score, rank() over (partition by sub_name order by score desc) from t_score sc

 S_ID S_NAME SUB_NAME SCORE 名次 4 杨过 JAVA 90.00 1 4 杨过 Oracle 77.00 1 2 李4 Oracle 77.00 1 3 张3丰 Oracle 0.00 3 5 mike c++ 80.00 1 3 张3丰 数学 1 2 李4 数学 80.00 2 1 张3 数学 0.00 3 3 张3丰 体育 120.00 1 1 张3 语文 80.00 1 2 李4 语文 50.00 2 3 张3丰 语文 10.00 3

Inquire the top 2 in each subject (ranking by district)

Similar: News table, seek the top 3 news with click-through rate.
Commodity list, seeking the top 10 commodities in sales of various categories.

``````
select * from (
select sc.s_id,sc.s_name,sub_name,sc.score,
dense_rank() over
(partition by sub_name order by score desc)  Ranking
from t_score sc
) x
where x. Ranking <=2``````

 S_ID S_NAME SUB_NAME SCORE 名次 4 杨过 JAVA 90.00 1 4 杨过 Oracle 77.00 1 2 李4 Oracle 77.00 1 3 张3丰 Oracle 0.00 2 5 mike c++ 80.00 1 3 张3丰 数学 1 2 李4 数学 80.00 2 3 张3丰 体育 120.00 1 1 张3 语文 80.00 1 2 李4 语文 50.00 2

Check the total score of each student select s_id,s_name,sum(score) sum_score from t_score group by s_id,s_name

 S_ID S_NAME SUM_SCORE 1 张3 80.00 2 李4 207.00 3 张3丰 130.00 4 杨过 167.00 5 mike 80.00

Inquire about the ranking of each student according to the total score select x.*, Ranking of rank () over (order by sum_score desc) from ( select s_id,s_name,sum(score) sum_score from t_score group by s_id,s_name ) x

 S_ID S_NAME SUM_SCORE 名次 2 李4 207.00 1 4 杨过 167.00 2 3 张3丰 130.00 3 1 张3 80.00 4 5 mike 80.00 4

Syntax: rank () over (order by sort field order) rank () over (partition by Packet Field order by Sort Field Order) 1. Sequence: ascdesc ranking is business-related: Example: Looking for excellent students: Grade: Descending Late Times: Ascending 2. Partition field: According to what field to partition. Question: What is the difference between partitioning and grouping? Partitioning only ranks the original data (the number of records remains unchanged), Grouping is to aggregate statistics of original data (the number of records becomes smaller, and each group returns one). Note: Aggregation.

Script:

``````
create table t_score
(
autoid number primary key,
s_id  number(3),
s_name char(8) not null,
sub_name varchar2(20),
score number(10,2)
);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (8, 1, ' Zhang 3 ', ' Language ', 80);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (9, 2, ' Li 4 ', ' Mathematics ', 80);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (10, 1, ' Zhang 3 ', ' Mathematics ', 0);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (11, 2, ' Li 4 ', ' Language ', 50);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (12, 3, ' Zhang 3 Abundance  ', ' Language ', 10);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (13, 3, ' Zhang 3 Abundance  ', ' Mathematics ', null);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (14, 3, ' Zhang 3 Abundance  ', ' Sports ', 120);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (15, 4, ' Yang Guo  ', 'java', 90);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (16, 5, 'mike ', 'c++', 80);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (3, 3, ' Zhang 3 Abundance  ', 'oracle', 0);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (4, 4, ' Yang Guo  ', 'oracle', 77);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (17, 2, ' Li 4 ', 'oracle', 77);
commit;``````

Related articles: