Shulou(Shulou.com)06/01 Report--

ROW_NUMBER()OVER() is an Oracle SQL analysis function, mainly used to sort the data groups to be queried.

use method

ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col2）

Group columns col1, col2 sort operations.

Examples:

SQL> SELECT 2 ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY sal, empno) AS rn, 3 empno, ename, sal, deptno 4 FROM emp; RN EMPNO ENAME SAL DEPTNO---------- ---------- ---------- ---------- ---------- 1 7934 MILLER 1300 10 2 7782 CLARK 2450 10 3 7839 KING 5000 10 1 7369 SMITH 800 20 2 7876 ADAMS 1100 20 3 7566 JONES 2975 20 4 7788 SCOTT 3000 20 5 7902 FORD 3000 20 1 7900 JAMES 950 30 2 7521 WARD 1250 30 3 7654 MARTIN 1250 30 RN EMPNO ENAME SAL DEPTNO---------- ---------- ---------- ---------- ---------- 4 7844 TURNER 1500 30 5 7499 ALLEN 1600 30 6 7698 BLAKE 2850 3014 rows selected.

It can also be used independently to sort results

Compare this with Order by:

SQL> SELECT empno, ename, sal, 2 ROW_NUMBER()OVER(ORDER BY sal, empno) AS rn 3 FROM emp; EMPNO ENAME SAL RN---------- ---------- ---------- ---------- 7369 SMITH 800 1 7900 JAMES 950 2 7876 ADAMS 1100 3 7521 WARD 1250 4 7654 MARTIN 1250 5 7934 MILLER 1300 6 7844 TURNER 1500 7 7499 ALLEN 1600 8 7782 CLARK 2450 9 7698 BLAKE 2850 10 7566 JONES 2975 11 EMPNO ENAME SAL RN---------- ---------- ---------- ---------- 7788 SCOTT 3000 12 7902 FORD 3000 13 7839 KING 5000 1414 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 3145491563---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 14 | 462 | 4 (25)| 00:00:01 || 1 | WINDOW SORT | | 14 | 462 | 4 (25)| 00:00:01 || 2 | TABLE ACCESS FULL| EMP | 14 | 462 | 3 (0)| 00:00:01 |---------------------------------------------------------------------------Note----- - dynamic sampling used for this statement (level=2)Statistics---------------------------------------------------------- 5 recursive calls 0 db block gets 16 consistent gets 1 physical reads 0 redo size 1049 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 14 rows processedSQL> SELECT empno, ename, sal 2 FROM emp 3 ORDER BY sal, empno; EMPNO ENAME SAL---------- ---------- ---------- 7369 SMITH 800 7900 JAMES 950 7876 ADAMS 1100 7521 WARD 1250 7654 MARTIN 1250 7934 MILLER 1300 7844 TURNER 1500 7499 ALLEN 1600 7782 CLARK 2450 7698 BLAKE 2850 7566 JONES 2975 EMPNO ENAME SAL---------- ---------- ---------- 7788 SCOTT 3000 7902 FORD 3000 7839 KING 500014 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 150391907---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 14 | 462 | 4 (25)| 00:00:01 || 1 | SORT ORDER BY | | 14 | 462 | 4 (25)| 00:00:01 || 2 | TABLE ACCESS FULL| EMP | 14 | 462 | 3 (0)| 00:00:01 |---------------------------------------------------------------------------Note----- - dynamic sampling used for this statement (level=2)Statistics---------------------------------------------------------- 4 recursive calls 0 db block gets 16 consistent gets 1 physical reads 0 redo size 943 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 14 rows processed

As opposed to ORDER BY clauses, the sorting results are the same, but using ROW_NUMBER()OVER() produces RN columns that facilitate row selection in some list programs.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.