ROW_NUMBER(), RANK(), DENSE_RANK() WITH EXAMPLE

View All Scripts Login to Run Script

Script Name ROW_NUMBER(), RANK(), DENSE_RANK() WITH EXAMPLE
Description ROW_NUMBER(), RANK(), DENSE_RANK() WITH EXAMPLE
Area SQL General
Contributor SQL for Hierarchical Format
Created Thursday August 31, 2017

Statement 1

CREATE TABLE "AB_EMPLOYEE"  
   (	"EMP_ID" VARCHAR2(5 BYTE),  
	"EMP_NAME" VARCHAR2(20 BYTE),  
	"DEPT_ID" VARCHAR2(5 BYTE),  
	"EXPERTISE" VARCHAR2(50 BYTE),  
	"SALARY" NUMBER(10,2),  
	"RESULTS" VARCHAR2(10 BYTE) 
   )

Table created.

Statement 2

Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('5003','ABINASH','1','SCIENCE','PASS',50000)

1 row(s) inserted.

Statement 3

Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('5003','ABINASH','1','ENGLISH','PASS',50000)

1 row(s) inserted.

Statement 4

Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('5003','ABINASH','1','MATH','PASS',50000)

1 row(s) inserted.

Statement 5

Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('107','AMARESH','2','MATH','PASS',50000)

1 row(s) inserted.

Statement 6

Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('107','AMARESH','2','ENGLISH','PASS',50000)

1 row(s) inserted.

Statement 7

Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('105','JYOTI','3','MATH','FAIL',75000)

1 row(s) inserted.

Statement 8

Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('105','JYOTI','3','ENGLISH','PASS',75000)

1 row(s) inserted.

Statement 9

Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('7003','NISHAD','2','ENGLISH','FAIL',70000)

1 row(s) inserted.

Statement 10

Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('7003','NISHAD','2','MATH','PASS',70000)

1 row(s) inserted.

Statement 11

Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('6003','RAKESH','2','MATH','PASS',50556)

1 row(s) inserted.

Statement 12

Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('6003','RAKESH','2','ENGLISH','FAIL',50556)

1 row(s) inserted.

Statement 13

Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('104','RAVI','2','MATH','PASS',70000)

1 row(s) inserted.

Statement 14

Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('104','RAVI','2','ENGLISH','PASS',70000)

1 row(s) inserted.

Statement 15

Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('106','REDDY','2','MATH','FAIL',80000)

1 row(s) inserted.

Statement 16

Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('106','REDDY','2','ENGLISH','PASS',80000)

1 row(s) inserted.

Statement 17

SELECT * FROM AB_EMPLOYEE


EMP_ID EMP_NAME DEPT_ID EXPERTISE SALARY RESULTS
5003 ABINASH 1 SCIENCE 50000 PASS
5003 ABINASH 1 ENGLISH 50000 PASS
5003 ABINASH 1 MATH 50000 PASS
107 AMARESH 2 MATH 50000 PASS
107 AMARESH 2 ENGLISH 50000 PASS
105 JYOTI 3 MATH 75000 FAIL
105 JYOTI 3 ENGLISH 75000 PASS
7003 NISHAD 2 ENGLISH 70000 FAIL
7003 NISHAD 2 MATH 70000 PASS
6003 RAKESH 2 MATH 50556 PASS
6003 RAKESH 2 ENGLISH 50556 FAIL
104 RAVI 2 MATH 70000 PASS
104 RAVI 2 ENGLISH 70000 PASS
106 REDDY 2 MATH 80000 FAIL
106 REDDY 2 ENGLISH 80000 PASS

15 rows selected.

EMP_ID	EMP_NAME	DEPT_ID	EXPERTISE	SALARY	RESULTS
5003	ABINASH	1	SCIENCE	50000	PASS
5003	ABINASH	1	ENGLISH	50000	PASS
5003	ABINASH	1	MATH	50000	PASS
107	AMARESH	2	MATH	50000	PASS
107	AMARESH	2	ENGLISH	50000	PASS
105	JYOTI	3	MATH	75000	FAIL
105	JYOTI	3	ENGLISH	75000	PASS
7003	NISHAD	2	ENGLISH	70000	FAIL
7003	NISHAD	2	MATH	70000	PASS
6003	RAKESH	2	MATH	50556	PASS
6003	RAKESH	2	ENGLISH	50556	FAIL
104	RAVI	2	MATH	70000	PASS
104	RAVI	2	ENGLISH	70000	PASS
106	REDDY	2	MATH	80000	FAIL
106	REDDY	2	ENGLISH	80000	PASS

Statement 18

SELECT EMP_ID, DEPT_ID,EXPERTISE,SALARY,  
  
SUM(SALARY) OVER (PARTITION BY DEPT_ID) AS DEPT_SALARY,  
  
SUM(SALARY) OVER (PARTITION BY EXPERTISE) AS EXPERTISE_SALARY,  
  
ROW_NUMBER() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC ) AS ROW_NUM ,  
  
RANK() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC ) AS RANK_NUM,  
  
DENSE_RANK() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC ) AS D_RANK_NUM  
  
FROM AB_EMPLOYEE  
  
ORDER BY DEPT_ID,SALARY DESC


EMP_ID DEPT_ID EXPERTISE SALARY DEPT_SALARY EXPERTISE_SALARY ROW_NUM RANK_NUM D_RANK_NUM
5003 1 SCIENCE 50000 150000 50000 1 1 1
5003 1 MATH 50000 150000 445556 2 1 1
5003 1 ENGLISH 50000 150000 445556 3 1 1
106 2 ENGLISH 80000 641112 445556 1 1 1
106 2 MATH 80000 641112 445556 2 1 1
7003 2 ENGLISH 70000 641112 445556 3 3 2
104 2 ENGLISH 70000 641112 445556 4 3 2
7003 2 MATH 70000 641112 445556 5 3 2
104 2 MATH 70000 641112 445556 6 3 2
6003 2 MATH 50556 641112 445556 7 7 3
6003 2 ENGLISH 50556 641112 445556 8 7 3
107 2 MATH 50000 641112 445556 9 9 4
107 2 ENGLISH 50000 641112 445556 10 9 4
105 3 MATH 75000 150000 445556 1 1 1
105 3 ENGLISH 75000 150000 445556 2 1 1

15 rows selected.

EMP_ID	DEPT_ID	EXPERTISE	SALARY	DEPT_SALARY	EXPERTISE_SALARY	ROW_NUM	RANK_NUM	D_RANK_NUM
5003	1	SCIENCE	50000	150000	50000	1	1	1
5003	1	MATH	50000	150000	445556	2	1	1
5003	1	ENGLISH	50000	150000	445556	3	1	1
106	2	ENGLISH	80000	641112	445556	1	1	1
106	2	MATH	80000	641112	445556	2	1	1
7003	2	ENGLISH	70000	641112	445556	3	3	2
104	2	ENGLISH	70000	641112	445556	4	3	2
7003	2	MATH	70000	641112	445556	5	3	2
104	2	MATH	70000	641112	445556	6	3	2
6003	2	MATH	50556	641112	445556	7	7	3
6003	2	ENGLISH	50556	641112	445556	8	7	3
107	2	MATH	50000	641112	445556	9	9	4
107	2	ENGLISH	50000	641112	445556	10	9	4
105	3	MATH	75000	150000	445556	1	1	1
105	3	ENGLISH	75000	150000	445556	2	1	1

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database