Oracle PL/SQL Tutorial/Analytical Functions/ROWNUM
Содержание
Depict the rank of the salaries with ROWNUM
<source lang="sql">
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> 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> 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> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped.</source>
Desacending order
<source lang="sql">
SQL> SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL primary key, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SQL> SQL> SELECT first_name, salary, ROWNUM Toprank
2 FROM (SELECT first_name, salary FROM employee ORDER BY salary desc) 3 ORDER BY first_name 4 /
FIRST_NAME SALARY TOPRANK
---------- ----------
Alison 6661.78 2 Celia 2344.78 5 David 7897.78 1 James 6544.78 3 James 1232.78 8 Jason 1234.56 7 Linda 4322.78 4 Robert 2334.78 6 8 rows selected. SQL> SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL>SQL></source>
To depict the rank of the salaries in descending order with ROWNUM
<source lang="sql">
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> 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 desc)
3 /
Emp # Name Salary RANK
--------------- ---------- ----------
8 Joke 8765 1
9 Jack 7896 2
5 Jane 7654 3
6 James 5679 4
7 Jodd 5438 5
3 Joe 4321 6
4 Tom 2413 7
2 John 2341 8
1 Jason 1234 9
9 rows selected. SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped.</source>
Using the ROWNUM function
- ROWNUM is a pseudo-column and is computed as rows are retrieved.
- Since ROWNUM is computed as rows are retrieved, it is somewhat limited.
- ROWNUM numbering takes place before the ordering.
<source lang="sql">
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> 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, ename, orig_salary, ROWNUM
2 FROM employee ORDER BY orig_salary
3 /
EMPNO ENAME ORIG_SALARY ROWNUM
--------------- ----------- ----------
1 Jason 1234 1
2 John 2341 2
4 Tom 2413 4
3 Joe 4321 3
7 Jodd 5438 7
6 James 5679 6
5 Jane 7654 5
9 Jack 7896 9
8 Joke 8765 8
9 rows selected. SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>
