SQL statement used to obtain the first n records of each group after grouping in oracle: rownumber () over ()

The SQL statement for the first n records of each group after grouping in oracle: rownumber () over () row_number () OVER (PARTITION BY COL1 ORDER BY COL2) indicates grouping BY COL1, sort by COL2 in the group, and the value calculated by this function indicates the sequential number after sorting in each group (the continuous and unique in the group ). the difference between rownum and rownum is that when rownum is used for sorting, a pseudo-column rownum is added to the result set before sorting, this function sorts the clause before calculating the row number. row_number () is similar to rownum and more powerful (it can be sorted from 1 on each group ). rank () is the Skip sorting. When there are two second names, the next is the fourth one (also in each group ). dense_rank () l is a continuous sorting, with two second names still followed by the third. In contrast, row_number has no duplicate value. www.2cto.com lag (arg1, arg2, arg3): arg1 is the expression returned from other rows. arg2 is the offset of the current row partition to be retrieved. Is a positive offset, and the number of rows in the past is retrieved. Arg3 is the value returned when the number indicated by arg2 exceeds the group range. Let's look at several SQL statements: Statement 1: 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 24 as sale, 6 as cnt from dual union select 50 as sale, 5 as cnt from dual union select-20 as sale, 2 as cnt from dual union select 40 as sale, 8 as cnt from dual); execution result: www.2cto.com sort sale/CNT ---------- 1 10 2 5 3 4 4-10 5-20 Statement 2: query the employee's salary, SORT by Department sel Ect 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. Www.2cto.com Statement 3: query the maximum salary of 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 ---------- -------------------- ---------- 10 KING 5000 20 SCOTT 3000 30 BLAKE 2850 has selected 3 rows. Statement 4: select deptno, sal, rank () over (partition by deptno order by sal) as rank_order from scott. emp order by deptno; www.2cto.com 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 2 30 1500 4 30 1600 5 30 2850 6 14 rows have been selected. Statement 5: select deptno, sal, dense_rank () over (partition by deptno order by sal) as dense_rank_order from scott. emp order by deptn; execution result: www.2cto.com 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 3000 4 30 950 1 1250 2 30 1250 2 30 1500 3 30 1600 4 30 2850 5 select 14 rows. Statement 6: select deptno, ename, sal, lag (ename, 1, null) over (partition by deptno order by ename) as lag _ from scott. emp order by deptno; www.2cto.com execution result: deptno ename sal lag _ ---------- -------------------- ---------- ------------------ 10 CLARK 2450 10 KING 5000 CLARK 10 MILLER 1300 KING 20 ADAMS 1100 20 FORD 3000 ADAMS 20 JONES 2975 FORD 20 SCOTT 3000 JONES 20 SMITH 800 SCOTT 30 ALLEN 1600 30 BLAKE 2 850 ALLEN 30 JAMES 950 blke 30 MARTIN 1250 JAMES 30 TURNER 1500 MARTIN 30 WARD 1250 TURNER www.2cto.com has selected 14 rows.