Oracle analytic function row_number () uses

1, Row_number () over () sorting function:

(1) Row_number () over () Grouping sorting function:

When using the Row_number () over () function, the grouping in over () and the execution of the sort are later than the where group by order by.

Partition by is used to group the result set, and if it is not specified then it takes the entire result set as a grouping, which differs from the aggregation function in that it can return multiple records in a group, whereas aggregate functions generally have only one record that reflects the statistical value.

For example: EMP, sort by department grouping.

Sql> Select Empno,deptno,sal, Row_number () over (partition by deptno ORDER BY sal Desc) rank from EMP;

   EMPNO     DEPTNO     SAL        rank---------- ---------- ---------- ----------       7839   10    5000  1       7782   10    2450  2       7934   10    1300  3      7788    20    3000  1      7902    20    3000  2      7566    20    2975  3      7876   20     1100  4      7369   20      800&nbsP; 5      7698   30    2850  1       7499   30    1600  2       7844   30    1500  3       7654   30    1250  4       7521   30    1250  5       7900   30     950  614 rows selected.

(2) Sort the results of the query: (No grouping)

Sql> Select Empno,deptno,sal, Row_number () over (order by Sal Desc) rank from EMP;

 EMPNO     DEPTNO     SAL        rank---------- ---------- ---------- ----------       7839   10    5000  1      7902    20    3000  2      7788    20    3000  3      7566    20    2975  4      7698   30     2850  5      7782   10     2450  6      7499   30     1600  7      7844   30     1500  8      7934   10    1300  9       7521   30    1250 10       7654   30    1250 11       7876   20    1100 12      7900    30     950 13      7369    20     800 1414 rows selected.

Row_number () over () is about the same as rownum, which is a bit more powerful (it can be sorted from 1 in each grouping).

2, Rank () over () is the jump sort, there are two second place next is the fourth place (also in the various groups).

Sql> Select Empno,deptno,sal, Rank () over (partition by deptno ORDER BY sal Desc) rank from EMP;

 EMPNO     DEPTNO     SAL        rank---------- ---------- ---------- ----------       7839   10    5000  1      7782    10    2450  2      7934    10    1300  3      7788    20    3000  1      7902   20     3000  1                     7566   20    2975 The   3          --jumped        7876   20    1100  4      7369   20      800  5      7698   30     2850  1      7499   30     1600  2      7844   30     1500  3      7654   30     1250  4      7521   30    1250   4      7900   30     950   614 rows selected.

Sql> Select Empno,deptno,sal, Rank () over (order by Sal Desc) rank from EMP;

 EMPNO     DEPTNO     SAL        rank---------- ---------- ---------- ----------       7839   10    5000  1      7902    20    3000  2      7788    20    3000  2          7566   20    2975  4        The  --jumped       7698   30    2850   5      7782   10    2450  6       7499   30    1600  7       7844   30    1500  8      7934    10    1300  9      7521    30    1250 10      7654   30     1250 10      7876   20     1100 12      7900   30      950 13      7369   20     800  1414 rows selected.

3, Dense_rank () over () is a sequential order, there are two second in the third place is still followed. In contrast, Row_number does not have duplicate values.

Sql> Select Empno,deptno,sal, Dense_rank () over (order by Sal Desc) rank from EMP;

   EMPNO     DEPTNO     SAL        rank---------- ---------- ---------- ----------       7839   10    5000  1       7902   20    3000  2       7788   20    3000  2      7566    20    2975  3      7698    30    2850  4      7782    10    2450  5      7499   30     1600  6      7844   30     1500 &nbSp;7      7934   10    1300  8       7521   30    1250  9       7654   30    1250  9       7876   20    1100 10       7900   30     950 11       7369   20     800 1214 rows selected.

Sql> Select Empno,deptno,sal, Dense_rank () over (partition by deptno ORDER BY sal Desc) rank from EMP;

empno     deptno     sal        rank---------- ---------- ---------- ----------      7839    10    5000  1      7782    10    2450  2      7934    10    1300  3      7788   20     3000  1      7902   20     3000  1      7566   20     2975  2    --not jumping       7876    20    1100  3      7369   20      800  4      7698   30    2850   1      7499   30    1600   2      7844   30    1500  3       7654   30    1250  4       7521   30    1250  4       7900   30     950  514 rows  selected.

Using Row_number to delete duplicate data

---Suppose you have a,b,c three columns in the Table tab, you can use the following statement to delete duplicate rows that have the same a,b,c.

DELETE from (select Year,quarter,results,row_number () over (partition by Year,quarter,results order by Year,quarter, RESULTS) as Row_no from SALE)

WHERE row_no>1

Reference: https://www.cnblogs.com/alsf/p/6344197.html

Oracle analytic function row_number () uses