SQL (Oracle) Gets the packet-after Max-value record

SELECT * FROM
(select T.*, Row_number () over (partition by Group field order by sort field desc) rn
From TableName t)
where rn=1

Row_number () over (PARTITION by COL1 ORDER by COL2) indicates that, according to COL1 grouping, sorting is based on COL2 within the grouping, and the value computed by this function represents the sequential number of each set of internally ordered (contiguous unique within the group).

The difference with rownum is that when you sort by using rownum, you first add a pseudo-column to the result set rownum then sort it, and this function sorts and then calculates the line number after it contains the sort clause.

Row_number () and rownum are much more powerful (they can be sorted from 1 in each grouping).

Rank () is a jumping sort, with two second names followed by fourth (also within each grouping).

Dense_rank () L is a sequential sort, with two second names still followed by third place. In contrast, Row_number does not have duplicate values.

Lag (ARG1,ARG2,ARG3):
Arg1 is an expression returned from another row
Arg2 is the offset of the current row partition that you want to retrieve. is a positive offset, when one retrieves the number of previous rows back.
Arg3 is the value returned when the number represented by Arg2 exceeds the range of the grouping.

Look at a few SQL statements:

Statement one:

Select Row_number () over (order by sale/cnt Desc) as sort, sale/cnt
From (
Select-60 as sale,3 as CNT from dual union
Select sale,6 as CNT from dual union
Select sale,5 as CNT from dual union
Select-20 as sale,2 as CNT from dual union
Select sale,8 as CNT from dual);

Execution Result:

SORT sale/cnt
---------- ----------
1 10
2 5
3 4
4-10
5-20

Statement two: Query employee's salary, sort by department

Select Ename,sal,row_number () over (partition by deptno ORDER BY Sal Desc) as Sal_order from Scott.emp;

Execution Result:

Ename SAL Sal_order
-------------------- ---------- ----------
KING 5000 1
CLARK 2450 2
MILLER 1300 3
SCOTT 3000 1
FORD 3000 2
JONES 2975 3
ADAMS 1100 4
SMITH 800 5
BLAKE 2850 1
ALLEN 1600 2
TURNER 1500 3
WARD 1250 4
MARTIN 1250 5
JAMES 950 6

14 rows have been selected.

Statement three: Query the maximum wage for each department

Select Deptno,ename,sal from
(select Deptno,ename,sal,row_number () over (partition by deptno ORDER BY Sal Desc) as Sal_order
From Scott.emp) where Sal_order <2;

Execution Result:

DEPTNO ename SAL
---------- -------------------- ----------
Ten KING 5000
SCOTT 3000
2850 BLAKE

3 rows have been selected.

Statement four:

Select Deptno,sal,rank () over (partition by Deptno ORDER by Sal) as Rank_order from Scott.emp order by Deptno;

Execution Result:

DEPTNO SAL Rank_order
---------- ---------- ----------
10 1300 1
10 2450 2
10 5000 3
20 800 1
20 1100 2
20 2975 3
20 3000 4
20 3000 4
30 950 1
30 1250 2
30 1250 2
30 1500 4
30 1600 5
30 2850 6

14 rows have been selected.

Statement Five:

Select Deptno,sal,dense_rank () over (partition by Deptno ORDER by Sal) as Dense_rank_order from Scott.emp order by DEPTN;

Execution Result:

DEPTNO SAL Dense_rank_order
---------- ---------- ----------------
10 1300 1
10 2450 2
10 5000 3
20 800 1
20 1100 2
20 2975 3
20 3000 4
20 3000 4
30 950 1
30 1250 2
30 1250 2
30 1500 3
30 1600 4
30 2850 5

14 rows have been selected.

Statement VI:

Select Deptno,ename,sal,lag (ename,1,null) over (partition by Deptno order by ename) as Lag_ from Scott.emp order by Deptno;

Execution Result:

DEPTNO ename SAL Lag_
---------- -------------------- ---------- --------------------
CLARK 2450
Ten KING CLARK
Ten MILLER 1300 KING
ADAMS 1100
FORD's ADAMS
JONES 2975 FORD
SCOTT JONES
SCOTT SMITH
ALLEN 1600
BLAKE 2850 ALLEN
JAMES 950 BLAKE
MARTIN 1250 JAMES
TURNER MARTIN
WARD 1250 TURNER

14 rows have been selected.

SQL (Oracle) Gets the packet-after Max-value record