ROW_NUMBER and PARTITION BY

CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));

INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',    NULL,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', NULL,    30);
INSERT INTO EMP VALUES (3, 'WARD',  'SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER',  2975,    20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER',  2850,    30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER',  2850,    10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST',  3000,    20);
INSERT INTO EMP VALUES (9, 'KING',  'PRESIDENT',3000,    10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500,    30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK',    1500,    20);

SQL>
SQL> select empno, ename, sal, deptno, row_number() over(partition by deptno ORDER by sal)
  2  from emp
  3  order by deptno;

     EMPNO ENAME             SAL     DEPTNO  ROW_NUMBER()OVER(PARTITIONBYDEPTNOORDERBYSAL)
---------- ---------- ---------- -------------------------------------------------------
         7 CLARK            2850         10                                            1
         9 KING             3000         10                                            2
        11 ADAMS            1500         20                                            1
         4 JONES            2975         20                                            2
         8 SCOTT            3000         20                                            3
         1 SMITH                         20                                            4
         3 WARD             1250         30                                            1
         5 MARTIN           1250         30                                            2
        10 TURNER           1500         30                                            3
         6 BLAKE            2850         30                                            4
         2 ALLEN                         30                                            5


11 rows selected.

SQL>