Oracle PL/SQL/Analytical Functions/ROW NUMBER — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
| |
Текущая версия на 09:56, 26 мая 2010
Содержание
- 1 Create a view based on row_number function
- 2 Decode the result from row_number over, partition by, order by
- 3 More Than One Analytical Function May Be Used in a Single Statement ROW_NUMBER(), RANK(), DENSE_RANK()
- 4 Open a cursor created by using row_number function
- 5 Rank() with nulls last
- 6 ROW_NUMBER function with an ordering on salary in descending order
- 7 row_number over partition by and order by
- 8 row_number() over (partition by deptno order)
- 9 ROW_NUMBER(): return a number with each row in a group, starting at 1
- 10 ROW_NUMBER() with order in descending order
- 11 Sum over and row_number() over
Create a view based on row_number function
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7369.00 SMITH CLERK 7902.00 17-DEC-80 800.00 20
7499.00 ALLEN SALESMAN 7698.00 20-FEB-81 1600.00 300.00 30
7521.00 WARD SALESMAN 7698.00 22-FEB-81 1250.00 500.00
7566.00 JONES MANAGER 7839.00 02-APR-81 2975.00 20
7654.00 MARTIN SALESMAN 7698.00 28-SEP-81 1250.00 1400.00 30
7698.00 BLAKE MANAGER 7839.00 01-MAY-81 2850.00
7782.00 CLARK MANAGER 7839.00 09-JUN-81 2450.00 10
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7788.00 SCOTT ANALYST 7566.00 09-DEC-82 3000.00 20
7839.00 KING PRESIDENT 17-NOV-81 5000.00 10
7844.00 TURNER SALESMAN 7698.00 08-SEP-81 1500.00 .00 30
7876.00 ADAMS CLERK 7788.00 12-JAN-83 1100.00 20
7900.00 JAMES CLERK 7698.00 03-DEC-81 950.00 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7902.00 FORD ANALYST 7566.00 03-DEC-81 3000.00 20
7934.00 MILLER CLERK 7782.00 23-JAN-82 1300.00 10
14 rows selected.
SQL>
SQL> create or replace view
2 emp_view
3 as
4 select mgr,
5 ename,
6 row_number() over ( partition by mgr
7 order by ename ) rn
8 from emp
9 /
View created.
SQL>
SQL> drop table emp;
Table dropped.
Decode the result from row_number over, partition by, order by
SQL>
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7369.00 SMITH CLERK 7902.00 17-DEC-80 800.00 20
7499.00 ALLEN SALESMAN 7698.00 20-FEB-81 1600.00 300.00 30
7521.00 WARD SALESMAN 7698.00 22-FEB-81 1250.00 500.00
7566.00 JONES MANAGER 7839.00 02-APR-81 2975.00 20
7654.00 MARTIN SALESMAN 7698.00 28-SEP-81 1250.00 1400.00 30
7698.00 BLAKE MANAGER 7839.00 01-MAY-81 2850.00
7782.00 CLARK MANAGER 7839.00 09-JUN-81 2450.00 10
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7788.00 SCOTT ANALYST 7566.00 09-DEC-82 3000.00 20
7839.00 KING PRESIDENT 17-NOV-81 5000.00 10
7844.00 TURNER SALESMAN 7698.00 08-SEP-81 1500.00 .00 30
7876.00 ADAMS CLERK 7788.00 12-JAN-83 1100.00 20
7900.00 JAMES CLERK 7698.00 03-DEC-81 950.00 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7902.00 FORD ANALYST 7566.00 03-DEC-81 3000.00 20
7934.00 MILLER CLERK 7782.00 23-JAN-82 1300.00 10
14 rows selected.
SQL>
SQL> set echo on
SQL>
SQL> select max(count(*)) from emp group by deptno, job;
MAX(COUNT(*))
-------------
4.00
SQL>
SQL> column deptno format 999
SQL> column sal_1 format 9999
SQL> column sal_2 format 9999
SQL> column sal_3 format 9999
SQL> column sal_4 format 9999
SQL> column ename_1 format a6
SQL> column ename_4 format a6
SQL>
SQL> select deptno, job,
2 max( decode( rn, 1, ename, null )) ename_1,
3 max( decode( rn, 1, sal, null )) sal_1,
4 max( decode( rn, 2, ename, null )) ename_2,
5 max( decode( rn, 2, sal, null )) sal_2,
6 max( decode( rn, 3, ename, null )) ename_3,
7 max( decode( rn, 3, sal, null )) sal_3,
8 max( decode( rn, 4, ename, null )) ename_4,
9 max( decode( rn, 4, sal, null )) sal_4
10 from ( select deptno, job, ename, sal,
11 row_number() over ( partition by deptno, job
12 order by sal, ename ) rn
13 from emp
14 )
15 group by deptno, job
16 /
DEPTNO JOB ENAME_ SAL_1 ENAME_2 SAL_2 ENAME_3 SAL_3 ENAME_ SAL_4
------ --------- ------ ----- ---------- ----- ---------- ----- ------ -----
10 CLERK MILLER 1300
MANAGER CLARK 2450
PRESIDENT KING 5000
20 ANALYST FORD 3000 SCOTT 3000
CLERK SMITH 800 ADAMS 1100
MANAGER JONES 2975
30 CLERK JAMES 950
MANAGER BLAKE 2850
SALESMAN MARTIN 1250 WARD 1250 TURNER 1500 ALLEN 1600
9 rows selected.
SQL> drop table emp;
Table dropped.
More Than One Analytical Function May Be Used in a Single Statement ROW_NUMBER(), RANK(), DENSE_RANK()
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 empno Number(3) NOT NULL, -- Employee ID
3 ename VARCHAR2(10 BYTE), -- Employee Name
4 hireDate DATE, -- Date Employee Hired
5 orig_salary Number(8,2), -- Orignal Salary
6 curr_salary Number(8,2), -- Current Salary
7 region VARCHAR2(1 BYTE) -- Region where employeed
8 )
9 /
Table created.
SQL>
SQL> create table job(
2 empno Number(3) NOT NULL, -- Employee ID
3 jobtitle VARCHAR2(10 BYTE) -- Employee job title
4 )
5 /
Table created.
SQL> -- prepare data for employee table
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(122,"Alison",to_date("19960321","YYYYMMDD"), 45000, 48000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(123, "James",to_date("19781212","YYYYMMDD"), 23000, 32000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(104,"Celia",to_date("19821024","YYYYMMDD"), 53000, 58000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(105,"Robert",to_date("19840115","YYYYMMDD"), 31000, 36000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(116,"Linda", to_date("19870730","YYYYMMDD"), 43000, 53000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(117,"David", to_date("19901231","YYYYMMDD"), 78000, 85000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(108,"Jode", to_date("19960917","YYYYMMDD"), 21000, 29000, "E")
3 /
1 row created.
SQL>
SQL> -- prepare data for job table
SQL>
SQL> insert into job(empno, jobtitle)
2 values(101, "Painter");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(122, "Tester");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(123, "Dediator");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(104, "Chemist");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(105, "Accountant");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(116, "Manager");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(117, "Programmer");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(108, "Developer");
1 row created.
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
Hit a key to continue
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
122 Alison 21-MAR-96 45000 48000 E
123 James 12-DEC-78 23000 32000 W
104 Celia 24-OCT-82 53000 58000 E
105 Robert 15-JAN-84 31000 36000 W
116 Linda 30-JUL-87 43000 53000 E
117 David 31-DEC-90 78000 85000 W
108 Jode 17-SEP-96 21000 29000 E
7 rows selected.
SQL> select * from job;
Hit a key to continue
EMPNO JOBTITLE
---------- ----------
101 Painter
122 Tester
123 Dediator
104 Chemist
105 Accountant
116 Manager
117 Programmer
108 Developer
8 rows selected.
SQL>
SQL> -- More Than One Analytical Function May Be Used in a Single Statement
SQL>
SQL>
SQL>
SQL> SELECT empno, ename, curr_salary,
2 ROW_NUMBER() OVER(ORDER BY curr_salary) rnum,
3 RANK() OVER(ORDER BY curr_salary) rank,
4 DENSE_RANK() OVER(ORDER BY curr_salary) drank
5 FROM employee
6 ORDER BY ename;
Hit a key to continue
EMPNO ENAME CURR_SALARY RNUM RANK DRANK
---------- ---------- ----------- ---------- ---------- ----------
122 Alison 48000 4 4 4
104 Celia 58000 6 6 6
117 David 85000 7 7 7
123 James 32000 2 2 2
108 Jode 29000 1 1 1
116 Linda 53000 5 5 5
105 Robert 36000 3 3 3
7 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> drop table job;
Table dropped.
SQL>
SQL> -- clean the table
SQL> drop table Employee;
Table dropped.
SQL>
SQL>
Open a cursor created by using row_number function
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7369.00 SMITH CLERK 7902.00 17-DEC-80 800.00 20
7499.00 ALLEN SALESMAN 7698.00 20-FEB-81 1600.00 300.00 30
7521.00 WARD SALESMAN 7698.00 22-FEB-81 1250.00 500.00
7566.00 JONES MANAGER 7839.00 02-APR-81 2975.00 20
7654.00 MARTIN SALESMAN 7698.00 28-SEP-81 1250.00 1400.00 30
7698.00 BLAKE MANAGER 7839.00 01-MAY-81 2850.00
7782.00 CLARK MANAGER 7839.00 09-JUN-81 2450.00 10
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7788.00 SCOTT ANALYST 7566.00 09-DEC-82 3000.00 20
7839.00 KING PRESIDENT 17-NOV-81 5000.00 10
7844.00 TURNER SALESMAN 7698.00 08-SEP-81 1500.00 .00 30
7876.00 ADAMS CLERK 7788.00 12-JAN-83 1100.00 20
7900.00 JAMES CLERK 7698.00 03-DEC-81 950.00 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7902.00 FORD ANALYST 7566.00 03-DEC-81 3000.00 20
7934.00 MILLER CLERK 7782.00 23-JAN-82 1300.00 10
14 rows selected.
SQL> set echo on
SQL>
SQL> variable x refcursor
SQL> set autoprint on
SQL>
SQL> begin
2 open :x for select mgr, ename, row_number() over ( partition by mgr order by ename ) rn from emp;
3 end;
4 /
PL/SQL procedure successfully completed.
MGR ENAME RN
-------- ---------- --------
7566.00 FORD 1.00
7566.00 SCOTT 2.00
7698.00 ALLEN 1.00
7698.00 JAMES 2.00
7698.00 MARTIN 3.00
7698.00 TURNER 4.00
7698.00 WARD 5.00
7782.00 MILLER 1.00
7788.00 ADAMS 1.00
7839.00 BLAKE 1.00
7839.00 CLARK 2.00
MGR ENAME RN
-------- ---------- --------
7839.00 JONES 3.00
7902.00 SMITH 1.00
KING 1.00
14 rows selected.
SQL> drop table emp;
Table dropped.
Rank() with nulls last
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 empno Number(3) NOT NULL, -- Employee ID
3 ename VARCHAR2(10 BYTE), -- Employee Name
4 hireDate DATE, -- Date Employee Hired
5 orig_salary Number(8,2), -- Orignal Salary
6 curr_salary Number(8,2), -- Current Salary
7 region VARCHAR2(1 BYTE) -- Region where employeed
8 )
9 /
Table created.
SQL>
SQL>
SQL> -- prepare data for employee table
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(122,"Alison",to_date("19960321","YYYYMMDD"), 45000, NULL, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(123, "James",to_date("19781212","YYYYMMDD"), 23000, 32000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(104,"Celia",to_date("19821024","YYYYMMDD"), NULL, 58000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(105,"Robert",to_date("19840115","YYYYMMDD"), 31000, NULL, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(116,"Linda", to_date("19870730","YYYYMMDD"), NULL, 53000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(117,"David", to_date("19901231","YYYYMMDD"), 78000, NULL, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(108,"Jode", to_date("19960917","YYYYMMDD"), 21000, 29000, "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
---------- ---------- --------- ----------- ----------- -
122 Alison 21-MAR-96 45000 E
123 James 12-DEC-78 23000 32000 W
104 Celia 24-OCT-82 58000 E
105 Robert 15-JAN-84 31000 W
116 Linda 30-JUL-87 53000 E
117 David 31-DEC-90 78000 W
108 Jode 17-SEP-96 21000 29000 E
7 rows selected.
SQL>
SQL>
SQL>
SQL> SELECT empno, ename, curr_salary,
2 RANK()
3 OVER(ORDER BY curr_salary desc NULLS LAST) salary
4 FROM employee
5 ORDER BY curr_salary desc NULLS LAST;
EMPNO ENAME CURR_SALARY SALARY
---------- ---------- ----------- ----------
104 Celia 58000 1
116 Linda 53000 2
123 James 32000 3
108 Jode 29000 4
117 David 5
122 Alison 5
105 Robert 5
7 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee;
Table dropped.
SQL>
SQL>
ROW_NUMBER function with an ordering on salary in descending order
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
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"), 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("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","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("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2334.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"), 2334.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"), 2334.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"), 2334.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 2334.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 2334.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2334.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 2334.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 2334.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 2334.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> -- ROW_NUMBER function with an ordering on salary in descending order:
SQL>
SQL> SELECT id, first_name, salary, ROW_NUMBER() OVER(ORDER BY salary desc) toprank FROM employee;
ID FIRST_NAME SALARY TOPRANK
---- ---------- ---------- ----------
04 Celia 2334.78 1
02 Alison 2334.78 2
03 James 2334.78 3
08 James 2334.78 4
05 Robert 2334.78 5
06 Linda 2334.78 6
07 David 2334.78 7
01 Jason 1234.56 8
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee;
Table dropped.
SQL>
SQL>
row_number over partition by and order by
SQL>
SQL> create table t
2 as
3 select object_name ename,
4 mod(object_id,50) deptno,
5 object_id sal
6 from all_objects
7 where rownum <= 1000
8 /
Table created.
SQL>
SQL>
SQL> select *
2 from (select deptno, ename, sal,
3 row_number() over (partition by deptno
4 order by sal desc)
5 rn from t)
6 where rn <= 3
7 /
DEPTNO ENAME SAL RN
------ ------------------------------ -------- --------
0 V_$LOCK 1050.00 1.00
V_$BUFFER_POOL_STATISTICS 1000.00 2.00
V_$DLM_ALL_LOCKS 950.00 3.00
1 V$LOCK 1051.00 1.00
V$BUFFER_POOL_STATISTICS 1001.00 2.00
V$DLM_ALL_LOCKS 951.00 3.00
2 V_$SESSTAT 1052.00 1.00
V_$INSTANCE_RECOVERY 1002.00 2.00
V_$DLM_LOCKS 952.00 3.00
DEPTNO ENAME SAL RN
------ ------------------------------ -------- --------
3 V$SESSTAT 1053.00 1.00
V$INSTANCE_RECOVERY 1003.00 2.00
V$DLM_LOCKS 953.00 3.00
4 V_$MYSTAT 1054.00 1.00
V_$CONTROLFILE 1004.00 2.00
V_$DLM_RESS 954.00 3.00
5 V$MYSTAT 1055.00 1.00
V$CONTROLFILE 1005.00 2.00
DEPTNO ENAME SAL RN
------ ------------------------------ -------- --------
5 V$DLM_RESS 955.00 3.00
6 V_$SUBCACHE 1056.00 1.00
V_$LOG 1006.00 2.00
V_$HVMASTER_INFO 956.00 3.00
7 V$SUBCACHE 1057.00 1.00
V$LOG 1007.00 2.00
V$HVMASTER_INFO 957.00 3.00
8 V_$SYSSTAT 1058.00 1.00
DEPTNO ENAME SAL RN
------ ------------------------------ -------- --------
8 V_$STANDBY_LOG 1008.00 2.00
V_$GCSHVMASTER_INFO 958.00 3.00
9 V$SYSSTAT 1059.00 1.00
V$STANDBY_LOG 1009.00 2.00
V$GCSHVMASTER_INFO 959.00 3.00
10 V_$STATNAME 1060.00 1.00
V_$DATAGUARD_STATUS 1010.00 2.00
V_$GCSPFMASTER_INFO 960.00 3.00
DEPTNO ENAME SAL RN
------ ------------------------------ -------- --------
11 V$STATNAME 1061.00 1.00
V$DATAGUARD_STATUS 1011.00 2.00
V$GCSPFMASTER_INFO 961.00 3.00
12 V_$OSSTAT 1062.00 1.00
V_$THREAD 1012.00 2.00
GV_$DLM_TRAFFIC_CONTROLLER 962.00 3.00
13 V$OSSTAT 1063.00 1.00
V$THREAD 1013.00 2.00
GV$DLM_TRAFFIC_CONTROLLER 963.00 3.00
DEPTNO ENAME SAL RN
------ ------------------------------ -------- --------
14 V_$ACCESS 1064.00 1.00
V_$PROCESS 1014.00 2.00
V_$DLM_TRAFFIC_CONTROLLER 964.00 3.00
15 V$ACCESS 1065.00 1.00
V$PROCESS 1015.00 2.00
V$DLM_TRAFFIC_CONTROLLER 965.00 3.00
16 V_$OBJECT_DEPENDENCY 1066.00 1.00
V_$BGPROCESS 1016.00 2.00
DEPTNO ENAME SAL RN
------ ------------------------------ -------- --------
16 V_$GES_ENQUEUE 966.00 3.00
17 V$OBJECT_DEPENDENCY 1067.00 1.00
V$BGPROCESS 1017.00 2.00
V$GES_ENQUEUE 967.00 3.00
18 V_$DBFILE 1068.00 1.00
V_$SESSION 1018.00 2.00
V_$GES_BLOCKING_ENQUEUE 968.00 3.00
19 V$DBFILE 1069.00 1.00
DEPTNO ENAME SAL RN
------ ------------------------------ -------- --------
19 V$SESSION 1019.00 2.00
V$GES_BLOCKING_ENQUEUE 969.00 3.00
20 V_$FILESTAT 1070.00 1.00
V_$LICENSE 1020.00 2.00
V_$GC_ELEMENT 970.00 3.00
21 V$FILESTAT 1071.00 1.00
V$LICENSE 1021.00 2.00
V$GC_ELEMENT 971.00 3.00
DEPTNO ENAME SAL RN
------ ------------------------------ -------- --------
22 V_$TEMPSTAT 1072.00 1.00
V_$TRANSACTION 1022.00 2.00
V_$CR_BLOCK_SERVER 972.00 3.00
23 V$TEMPSTAT 1073.00 1.00
V$TRANSACTION 1023.00 2.00
V$CR_BLOCK_SERVER 973.00 3.00
24 V_$LOGFILE 1074.00 1.00
V_$BSP 1024.00 2.00
V_$CURRENT_BLOCK_SERVER 974.00 3.00
DEPTNO ENAME SAL RN
------ ------------------------------ -------- --------
25 V$LOGFILE 1075.00 1.00
V$BSP 1025.00 2.00
V$CURRENT_BLOCK_SERVER 975.00 3.00
26 V_$FLASHBACK_DATABASE_LOGFILE 1076.00 1.00
V_$FAST_START_SERVERS 1026.00 2.00
V_$GC_ELEMENTS_W_COLLISIONS 976.00 3.00
27 V$FLASHBACK_DATABASE_LOGFILE 1077.00 1.00
V$FAST_START_SERVERS 1027.00 2.00
DEPTNO ENAME SAL RN
------ ------------------------------ -------- --------
27 V$GC_ELEMENTS_WITH_COLLISIONS 977.00 3.00
28 V_$FLASHBACK_DATABASE_LOG 1078.00 1.00
V_$FAST_START_TRANSACTIONS 1028.00 2.00
V_$FILE_CACHE_TRANSFER 978.00 3.00
29 V$FLASHBACK_DATABASE_LOG 1079.00 1.00
V$FAST_START_TRANSACTIONS 1029.00 2.00
V$FILE_CACHE_TRANSFER 979.00 3.00
30 V_$FLASHBACK_DATABASE_STAT 1080.00 1.00
DEPTNO ENAME SAL RN
------ ------------------------------ -------- --------
30 V_$LOCKED_OBJECT 1030.00 2.00
V_$TEMP_CACHE_TRANSFER 980.00 3.00
31 V$FLASHBACK_DATABASE_STAT 1081.00 1.00
V$LOCKED_OBJECT 1031.00 2.00
V$TEMP_CACHE_TRANSFER 981.00 3.00
32 V_$RESTORE_POINT 1082.00 1.00
V_$LATCH 1032.00 2.00
V_$CLASS_CACHE_TRANSFER 982.00 3.00
DEPTNO ENAME SAL RN
------ ------------------------------ -------- --------
33 V$RESTORE_POINT 1083.00 1.00
V$LATCH 1033.00 2.00
V$CLASS_CACHE_TRANSFER 983.00 3.00
34 V_$ROLLNAME 1084.00 1.00
V_$LATCH_CHILDREN 1034.00 2.00
V_$BH 984.00 3.00
35 V$ROLLNAME 1085.00 1.00
V$LATCH_CHILDREN 1035.00 2.00
V$BH 985.00 3.00
DEPTNO ENAME SAL RN
------ ------------------------------ -------- --------
36 V_$ROLLSTAT 1086.00 1.00
V_$LATCH_PARENT 1036.00 2.00
V_$LOCK_ELEMENT 986.00 3.00
37 V$ROLLSTAT 1087.00 1.00
V$LATCH_PARENT 1037.00 2.00
V$LOCK_ELEMENT 987.00 3.00
38 V_$UNDOSTAT 1088.00 1.00
V_$LATCHNAME 1038.00 2.00
DEPTNO ENAME SAL RN
------ ------------------------------ -------- --------
38 V_$LOCKS_WITH_COLLISIONS 988.00 3.00
39 V$UNDOSTAT 1089.00 1.00
V$LATCHNAME 1039.00 2.00
V$LOCKS_WITH_COLLISIONS 989.00 3.00
40 V_$SGA 1090.00 1.00
V_$LATCHHOLDER 1040.00 2.00
V_$FILE_PING 990.00 3.00
41 V$LATCHHOLDER 1041.00 1.00
DEPTNO ENAME SAL RN
------ ------------------------------ -------- --------
41 V$FILE_PING 991.00 2.00
V$SQL_OPTIMIZER_ENV 941.00 3.00
42 V_$LATCH_MISSES 1042.00 1.00
V_$TEMP_PING 992.00 2.00
V_$DLM_MISC 942.00 3.00
43 V$LATCH_MISSES 1043.00 1.00
V$TEMP_PING 993.00 2.00
V$DLM_MISC 943.00 3.00
DEPTNO ENAME SAL RN
------ ------------------------------ -------- --------
44 V_$SESSION_LONGOPS 1044.00 1.00
V_$CLASS_PING 994.00 2.00
V_$DLM_LATCH 944.00 3.00
45 V$SESSION_LONGOPS 1045.00 1.00
V$CLASS_PING 995.00 2.00
V$DLM_LATCH 945.00 3.00
46 V_$RESOURCE 1046.00 1.00
V_$INSTANCE_CACHE_TRANSFER 996.00 2.00
V_$DLM_CONVERT_LOCAL 946.00 3.00
DEPTNO ENAME SAL RN
------ ------------------------------ -------- --------
47 V$RESOURCE 1047.00 1.00
V$INSTANCE_CACHE_TRANSFER 997.00 2.00
V$DLM_CONVERT_LOCAL 947.00 3.00
48 V_$_LOCK 1048.00 1.00
V_$BUFFER_POOL 998.00 2.00
V_$DLM_CONVERT_REMOTE 948.00 3.00
49 V$_LOCK 1049.00 1.00
V$BUFFER_POOL 999.00 2.00
DEPTNO ENAME SAL RN
------ ------------------------------ -------- --------
49 V$DLM_CONVERT_REMOTE 949.00 3.00
150 rows selected.
SQL> drop table t;
Table dropped.
SQL>
row_number() over (partition by deptno order)
SQL>
SQL>
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 );
Table created.
SQL>
SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> select deptno,ename,sal,
2 row_number() over (partition by deptno order by sal desc) rn,
3 rank() over (partition by deptno order by sal desc ) rank,
4 dense_rank() over ( partition by deptno order by sal desc ) dense_rank
5 from emp
6 order by deptno, sal DESC
7 /
DEPTNO ENAME SAL RN RANK DENSE_RANK
---------- ---------- ---------- ---------- ---------- ----------
10 KING 5000 1 1 1
CLARK 2450 2 2 2
MILLER 1300 3 3 3
20 SCOTT 3000 1 1 1
FORD 3000 2 1 1
JONES 2975 3 3 2
ADAMS 1100 4 4 3
SMITH 800 5 5 4
30 BLAKE 2850 1 1 1
DEPTNO ENAME SAL RN RANK DENSE_RANK
---------- ---------- ---------- ---------- ---------- ----------
30 ALLEN 1600 2 2 2
TURNER 1500 3 3 3
MARTIN 1250 4 4 4
WARD 1250 5 4 4
JAMES 950 6 6 5
14 rows selected.
SQL>
SQL> update emp set sal = 3000 where ename = "JONES";
1 row updated.
SQL>
SQL> select deptno,ename,sal,
2 row_number() over (partition by deptno order by sal desc) rn,
3 rank() over (partition by deptno order by sal desc ) rank,
4 dense_rank() over ( partition by deptno order by sal desc ) dense_rank
5 from emp
6 order by deptno, sal DESC
7 /
DEPTNO ENAME SAL RN RANK DENSE_RANK
---------- ---------- ---------- ---------- ---------- ----------
10 KING 5000 1 1 1
CLARK 2450 2 2 2
MILLER 1300 3 3 3
20 JONES 3000 1 1 1
SCOTT 3000 2 1 1
FORD 3000 3 1 1
ADAMS 1100 4 4 2
SMITH 800 5 5 3
30 BLAKE 2850 1 1 1
DEPTNO ENAME SAL RN RANK DENSE_RANK
---------- ---------- ---------- ---------- ---------- ----------
30 ALLEN 1600 2 2 2
TURNER 1500 3 3 3
WARD 1250 4 4 4
MARTIN 1250 5 4 4
JAMES 950 6 6 5
14 rows selected.
SQL>
SQL> drop table emp;
Table dropped.
ROW_NUMBER(): return a number with each row in a group, starting at 1
SQL> CREATE TABLE all_sales (
2 year INTEGER,
3 month INTEGER,
4 prd_type_id INTEGER,
5 emp_id INTEGER ,
6 amount NUMBER(8, 2)
7 );
Table created.
SQL>
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,1 ,1 ,21 ,16034.84);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,2 ,1 ,21 ,15644.65);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,3 ,2 ,21 ,20167.83);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,4 ,2 ,21 ,25056.45);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,5 ,2 ,21 ,NULL);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,6 ,1 ,21 ,15564.66);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,7 ,1 ,21 ,15644.65);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,8 ,1 ,21 ,16434.82);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,9 ,1 ,21 ,19654.57);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,10 ,1 ,21 ,21764.19);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,11 ,1 ,21 ,13026.73);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,12 ,2 ,21 ,10034.64);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,1 ,2 ,22 ,16634.84);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,1 ,2 ,21 ,26034.84);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,2 ,1 ,21 ,12644.65);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,3 ,1 ,21 ,NULL);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,4 ,1 ,21 ,25026.45);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,5 ,1 ,21 ,17212.66);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,6 ,1 ,21 ,15564.26);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,7 ,2 ,21 ,62654.82);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,8 ,2 ,21 ,26434.82);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,9 ,2 ,21 ,15644.65);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,10 ,2 ,21 ,21264.19);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,11 ,1 ,21 ,13026.73);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,12 ,1 ,21 ,10032.64);
1 row created.
SQL>
SQL> select * from all_sales;
YEAR MONTH PRD_TYPE_ID EMP_ID AMOUNT
---------- ---------- ----------- ---------- ----------
2006 1 1 21 16034.84
2006 2 1 21 15644.65
2006 3 2 21 20167.83
2006 4 2 21 25056.45
2006 5 2 21
2006 6 1 21 15564.66
2006 7 1 21 15644.65
2006 8 1 21 16434.82
2006 9 1 21 19654.57
2006 10 1 21 21764.19
2006 11 1 21 13026.73
2006 12 2 21 10034.64
2005 1 2 22 16634.84
2005 1 2 21 26034.84
2005 2 1 21 12644.65
2005 3 1 21
2005 4 1 21 25026.45
2005 5 1 21 17212.66
2005 6 1 21 15564.26
2005 7 2 21 62654.82
2005 8 2 21 26434.82
2005 9 2 21 15644.65
2005 10 2 21 21264.19
2005 11 1 21 13026.73
2005 12 1 21 10032.64
25 rows selected.
SQL>
SQL> --ROW_NUMBER(): return a number with each row in a group, starting at 1
SQL>
SQL> SELECT
2 prd_type_id, SUM(amount),
3 ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC) AS row_number
4 FROM all_sales
5 GROUP BY prd_type_id
6 ORDER BY prd_type_id;
PRD_TYPE_ID SUM(AMOUNT) ROW_NUMBER
----------- ----------- ----------
1 227276.5 1
2 223927.08 2
SQL>
SQL> drop table all_sales;
Table dropped.
SQL>
SQL>
ROW_NUMBER() with order in descending order
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 empno Number(3) NOT NULL, -- Employee ID
3 ename VARCHAR2(10 BYTE), -- Employee Name
4 hireDate DATE, -- Date Employee Hired
5 orig_salary Number(8,2), -- Orignal Salary
6 curr_salary Number(8,2), -- Current Salary
7 region VARCHAR2(1 BYTE) -- Region where employeed
8 )
9 /
Table created.
SQL>
SQL>
SQL> -- prepare data for employee table
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(122,"Alison",to_date("19960321","YYYYMMDD"), 45000, NULL, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(123, "James",to_date("19781212","YYYYMMDD"), 23000, 32000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(104,"Celia",to_date("19821024","YYYYMMDD"), NULL, 58000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(105,"Robert",to_date("19840115","YYYYMMDD"), 31000, NULL, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(116,"Linda", to_date("19870730","YYYYMMDD"), NULL, 53000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(117,"David", to_date("19901231","YYYYMMDD"), 78000, NULL, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(108,"Jode", to_date("19960917","YYYYMMDD"), 21000, 29000, "E")
3 /
1 row created.
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
Hit a key to continue
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
122 Alison 21-MAR-96 45000 E
123 James 12-DEC-78 23000 32000 W
104 Celia 24-OCT-82 58000 E
105 Robert 15-JAN-84 31000 W
116 Linda 30-JUL-87 53000 E
117 David 31-DEC-90 78000 W
108 Jode 17-SEP-96 21000 29000 E
7 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> -- In descending order:
SQL>
SQL> SELECT empno, ename, curr_salary,
2 ROW_NUMBER() OVER(ORDER BY curr_salary desc) salary
3 FROM employee
4 ORDER BY curr_salary desc;
Hit a key to continue
EMPNO ENAME CURR_SALARY SALARY
---------- ---------- ----------- ----------
122 Alison 1
105 Robert 2
117 David 3
104 Celia 58000 4
116 Linda 53000 5
123 James 32000 6
108 Jode 29000 7
7 rows selected.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee;
Table dropped.
SQL>
SQL>
Sum over and row_number() over
SQL>
SQL> set echo on
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );
Table created.
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
1 row created.
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
1 row created.
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
1 row created.
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
1 row created.
SQL>
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7369.00 SMITH CLERK 7902.00 17-DEC-80 800.00 20
7499.00 ALLEN SALESMAN 7698.00 20-FEB-81 1600.00 300.00 30
7521.00 WARD SALESMAN 7698.00 22-FEB-81 1250.00 500.00
7566.00 JONES MANAGER 7839.00 02-APR-81 2975.00 20
7654.00 MARTIN SALESMAN 7698.00 28-SEP-81 1250.00 1400.00 30
7698.00 BLAKE MANAGER 7839.00 01-MAY-81 2850.00
7782.00 CLARK MANAGER 7839.00 09-JUN-81 2450.00 10
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7788.00 SCOTT ANALYST 7566.00 09-DEC-82 3000.00 20
7839.00 KING PRESIDENT 17-NOV-81 5000.00 10
7844.00 TURNER SALESMAN 7698.00 08-SEP-81 1500.00 .00 30
7876.00 ADAMS CLERK 7788.00 12-JAN-83 1100.00 20
7900.00 JAMES CLERK 7698.00 03-DEC-81 950.00 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7902.00 FORD ANALYST 7566.00 03-DEC-81 3000.00 20
7934.00 MILLER CLERK 7782.00 23-JAN-82 1300.00 10
14 rows selected.
SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
SQL>
SQL> break on deptno skip 1
SQL> select ename, deptno, sal,
2 sum(sal) over
3 (order by deptno, ename) running_total,
4 sum(sal) over
5 (partition by deptno
6 order by ename) department_total,
7 row_number() over
8 (partition by deptno
9 order by ename ) seq
10 from emp
11 order by deptno, ename
12 /
ENAME DEPTNO SAL RUNNING_TOTAL DEPARTMENT_TOTAL SEQ
---------- ------ -------- ------------- ---------------- --------
CLARK 10 2450.00 2450.00 2450.00 1.00
KING 5000.00 7450.00 7450.00 2.00
MILLER 1300.00 8750.00 8750.00 3.00
ADAMS 20 1100.00 9850.00 1100.00 1.00
FORD 3000.00 ######## 4100.00 2.00
JONES 2975.00 ######## 7075.00 3.00
SCOTT 3000.00 ######## ######## 4.00
SMITH 800.00 ######## ######## 5.00
ALLEN 30 1600.00 ######## 1600.00 1.00
ENAME DEPTNO SAL RUNNING_TOTAL DEPARTMENT_TOTAL SEQ
---------- ------ -------- ------------- ---------------- --------
BLAKE 30 2850.00 ######## 4450.00 2.00
JAMES 950.00 ######## 5400.00 3.00
MARTIN 1250.00 ######## 6650.00 4.00
TURNER 1500.00 ######## 8150.00 5.00
WARD 1250.00 ######## 9400.00 6.00
14 rows selected.
SQL>
SQL> drop table emp;
Table dropped.
SQL> drop table dept;
Table dropped.
SQL>