Row_number () over in Oracle (partition by order by) usage

This is often used in Oracle

Select Col1,col2, Row_number () over (partition by Colx order by Coly) from TABLE_NAME;;

This means that the values in the table are grouped according to Colx, and each group is sorted internally by Coly, and Row_number () returns the ordinal number of the record within the reorganization after sorting.

For example, we know there is an EMP table as follows:

Sql> SELECT *  fromSCOTT.     EMP; EMPNO ename JOB MGR hiredate SAL COMM D Eptno---------- ------------------------------ --------------------------- ---------- ------------------ ---------- ---- ------ ----------      7369SMITH Clerk7902  --DEC- the                  -                     -      7499ALLEN salesman7698  --FEB-Bayi                 the         -          -      7521WARD salesman7698  A-FEB-Bayi                1250         -          -      7566JONES MANAGER7839  Geneva-APR-Bayi                2975                     -      7654MARTIN salesman7698  --SEP-Bayi                1250       1400          -      7698BLAKE MANAGER7839  on-May-Bayi                2850                     -      7782CLARK MANAGER7839  the-June-Bayi                2450                    Ten      7788SCOTT ANALYST7566  +-APR- the                 the                     -      7839KING President --NOV-Bayi                 the                    Ten      7844TURNER salesman7698  ,-SEP-Bayi                 the          0          -      7876ADAMS Clerk7788  at-May- the                1100                     -      7900JAMES Clerk7698 Geneva-DEC-Bayi                 950                     -      7902FORD ANALYST7566 Geneva-DEC-Bayi                 the                     -      7934MILLER Clerk7782  at-JAN- the                1300                    Ten

Use the following statement to display the following:

Sql> SELECTEname,deptno,empno,row_number () Over(PARTITION byDEPTNOORDER  byEMPNO) fromSCOTT. EMP; ename DEPTNO EMPNO row_number () Over(partitionbydeptnoorderbyempno)------------------------------ ---------- ---------- -----------------------------------------------CLARKTen       7782                                               1KINGTen       7839                                               2MILLERTen       7934                                               3SMITH -       7369                                               1JONES -       7566                                               2SCOTT -       7788                                               3ADAMS -       7876                                               4FORD -       7902                                               5ALLEN -       7499                                               1WARD -       7521                                               2MARTIN -       7654                                               3BLAKE -       7698                                               4TURNER -       7844                                               5JAMES -       7900                                               6