SQL implementation sorting methods and differences for multiple criteria grouping

Transferred from: http://blog.csdn.net/winer2008/article/details/4283539

Rank,dense_rank,row_number differences

One: Syntax (usage):
Rank () over ([partition by col1] ORDER by col2)
Dense_rank () over ([partition by col1] ORDER by col2)
Row_number () over ([partition by col1] ORDER by col2)
where [partition by col1] can be omitted.

Two: Difference
All three analysis functions are sorted from 1 in the col1 group

Row_number () is a sort that does not have a duplicate value (even if two days of record equality are not duplicated), it can be used to achieve paging
Dense_rank () is a sequential sort, with two second names still following the third place
Rank () is a jump shoot, two second place is fourth place

The theory is not much to say, read the case, a moment to understand

Sql> CREATE TABLE T (
2 name VARCHAR2 (10),
3 Score Number (3));

Table created

sql> insert INTO T (Name,score)
2 Select ' Language ', dual union ALL
3 Select ' Language ', dual union ALL
4 Select ' Language ', dual union ALL
5 Select ' Language ', dual union ALL
6 Select ' Math ', dual UNION ALL
7 Select ' Math ', dual UNION ALL
8 Select ' Math ', dual UNION ALL
9 Select ' Math ', dual UNION ALL
Ten select ' Math ', from dual union all
Select ' Chinese ', from dual
12/

Rows inserted

Sql> select * from T;

NAME Score
---------- -----
Language 60
Language 90
Language 80
Language 80
Mathematics 67
Mathematics 77
Mathematics 78
Mathematics 88
Mathematics 99
Language 70

Rows selected

Sql> Select Name,score,rank () over (partition by name, order by score), TT from T;

NAME score TT
---------- ----- ----------
Mathematics 67 1
Mathematics 77 2
Mathematics 78 3
Mathematics 88 4
Mathematics 99 5
Language 60 1
Language 70 2
Language 3 <----
Language 3 <----
Language 90 5

Rows selected

Sql> Select Name,score,dense_rank () over (partition by name, order by score), TT from T;

NAME score TT
---------- ----- ----------
Mathematics 67 1
Mathematics 77 2
Mathematics 78 3
Mathematics 88 4
Mathematics 99 5
Language 60 1
Language 70 2
Language 3 <----
Language 3 <----
Language 90 4

Rows selected

Sql> Select Name,score,row_number () over (partition by name, order by score), TT from T;

NAME score TT
---------- ----- ----------
Mathematics 67 1
Mathematics 77 2
Mathematics 78 3
Mathematics 88 4
Mathematics 99 5
Language 60 1
Language 70 2
Language 3 <----
Language 4 <----
Language 90 5

Rows selected

Sql> Select Name,score,rank () over (order by score) TT from T;

NAME score TT
---------- ----- ----------
Language 60 1
Mathematics 67 2
Language 70 3
Mathematics 77 4
Mathematics 78 5
Language 80 6
Language 80 6
Mathematics 88 8
Language 90 9
Mathematics 99 10

Rows selected

Everyone should understand! Oh! Next look at the app

A: Dense_rank------------------query the top three of each course

Select Name,score from (select Name,score,dense_rank () over (partition by name ORDER BY score desc) TT from T) x where x.tt <=3


NAME Score
---------- -----
Mathematics 99
Mathematics 88
Mathematics 78
Language 90
Language 80
Language 80

6 Rows selected

Second: Rank------------------the number of students who scored 70 in Chinese.
Select Name,score,x.tt from (select Name,score,rank () over (partition by name ORDER BY score desc) TT from T) x where X.nam E= ' Chinese ' and x.score=70


NAME score TT
---------- ----- ----------
Language 70 4

Three: row_number —————— paging Query
Select xx.* from (select T.*,row_number () over (order BY score Desc) Rowno from T) xx where xx.rowno between 1 and 3;

NAME score ROWNO
---------- ----- ----------
Mathematics 99 1
Language 90 2
Mathematics 88 3

SQL implementation sorting methods and differences for multiple criteria grouping