Oracle PL/SQL Tutorial/Analytical Functions/ROWNUM
Содержание
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>
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.
Desacending order
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>
To depict the rank of the salaries in descending order 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>
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.
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.
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>