Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / Analytical Functions

 

Depict the rank of the salaries with ROWNUM

SQL> SQL> -- create demo table SQL> create table Employee(   2    EMPNO         NUMBER(3),   3    ENAME         VARCHAR2(15 BYTE),   4    HIREDATE      DATE,   5    ORIG_SALARY   NUMBER(6),   6    CURR_SALARY   NUMBER(6),   7    REGION        VARCHAR2(1 BYTE)   8  )   9  / Table created. SQL> SQL> -- prepare data SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_S ALARY,  REGION)   2               values (1,      'Jason', to_date('19960725','YYYYMMDD'), 1234,              8767,         'E')   3  / 1 row created. SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_S ALARY,  REGION)   2               values (2,      'John',  to_date('19970715','YYYYMMDD'), 2341,              3456,         'W')   3  / 1 row created. SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_S ALARY,  REGION)   2               values (3,      'Joe',   to_date('19860125','YYYYMMDD'), 4321,              5654,         'E')   3  / 1 row created. SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_S ALARY,  REGION)   2               values (4,      'Tom',   to_date('20060913','YYYYMMDD'), 2413,              6787,         'W')   3  / 1 row created. SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_S ALARY,  REGION)   2               values (5,      'Jane',  to_date('20050417','YYYYMMDD'), 7654,              4345,         'E')   3  / 1 row created. SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_S ALARY,  REGION)   2               values (6,      'James', to_date('20040718','YYYYMMDD'), 5679,              6546,         'W')   3  / 1 row created. SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_S ALARY,  REGION)   2               values (7,      'Jodd',  to_date('20030720','YYYYMMDD'), 5438,              7658,         'E')   3  / 1 row created. SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_S ALARY,  REGION)   2               values (8,      'Joke',  to_date('20020101','YYYYMMDD'), 8765,              4543,         'W')   3  / 1 row created. SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_S ALARY,  REGION)   2               values (9,      'Jack',  to_date('20010829','YYYYMMDD'), 7896,              1232,         'E')   3  / 1 row created. SQL> SQL> -- display data in the table SQL> select * from Employee   2  /      EMPNO ENAME           HIREDATE  ORIG_SALARY CURR_SALARY R ---------- --------------- --------- ----------- ----------- -          1 Jason           25-JUL-96        1234        8767 E          2 John            15-JUL-97        2341        3456 W          3 Joe             25-JAN-86        4321        5654 E          4 Tom             13-SEP-06        2413        6787 W          5 Jane            17-APR-05        7654        4345 E          6 James           18-JUL-04        5679        6546 W          7 Jodd            20-JUL-03        5438        7658 E          8 Joke            01-JAN-02        8765        4543 W          9 Jack            29-AUG-01        7896        1232 E 9 rows selected. SQL> SQL> SELECT empno "Emp #", ename "Name", orig_salary "Salary", ROWNUM rank   2  FROM (SELECT empno, ename, orig_salary FROM employee ORDER BY orig_salary)   3  /      Emp # Name                Salary       RANK ---------- --------------- ---------- ----------          1 Jason                 1234          1          2 John                  2341          2          4 Tom                   2413          3          3 Joe                   4321          4          7 Jodd                  5438          5          6 James                 5679          6          5 Jane                  7654          7          9 Jack                  7896          8          8 Joke                  8765          9 9 rows selected. SQL> SQL> -- clean the table SQL> drop table Employee   2  / Table dropped.