Row_number () OVER (partition by COL1 order by COL2)

Row_number () OVER (partition by COL1 order by COL2)
-- Indicates grouping by COL1 and sorting by COL2 within the group, and the value returned by this function indicates the sequential number after sorting within each group (the continuous and unique in the Group)

create table student (id int ,classes int ,score int);insert into student values(1,1,89);insert into student values(2,1,90);insert into student values(3,1,76);insert into student values(4,2,69);insert into student values(5,2,79);insert into student values(6,2,95);insert into student values(7,3,80);insert into student values(8,3,85);insert into student values(9,3,79);commit;

 

select t.* from student t;

-- Data is displayed
Id classes score
-------------------------------------------------------------
1 1 89
2 1 90
3 1 76
4 2 69
5 2 79
6 2 95
7 3 80
8 3 85
9 3 79

-- Requirement: the English score ranking of each class is displayed based on the group of classes.

-- Expected results:

Id classes score rank
-------------------------------------------------------------
3 1 76 1
1 1 89 2
2 1 90 3
4 2 69 1
5 2 79 2
6 2 95 3
9 3 79 1
7 3 80 2
8 3 85 3

-- SQL script:

SELECT *, Row_Number() OVER (partition by classes ORDER BY score desc) rank FROM student;

-- In the t_test table, the callid field does not repeat the data, which is more efficient than group by having count.

select t.*, t.rowid  from t_test t where t.rowid not in (select rid                         from (select t2.rowid rid,                                      row_number() over(partition by t2.callid order by t2.rowid desc) m                                 from t_test t2)                        where m <> 1)   and t.rowid not in (select rid                         from (select t2.rowid rid,                                      row_number() over(partition by t2.callid order by t2.rowid asc) m                                 from t_test t2)                        where m <> 1);