Performing top-N and bottom-N Queries in Oracle
TABLE employees
Name Null? Type
---------------- -------- -------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
Task:
Display top two salaries in each department.
1. Using analytic function ROW_NUMBER ():
SELECT employee_id, department_id, salary
FROM (SELECT employee_id, department_id, salary,
ROW_NUMBER () OVER (PARTITION BY department_id ORDER BY salary DESC)
r_no
FROM employees)
WHERE r_no <= 2
EMPLOYEE_ID DEPARTMENT_ID SALARY
200 10 4400
201 20 13000
202 20 6000
114 30 11000
115 30 3100
203 40 6500
...
100 90 24000
101 90 17000
2. Using analytic function RANK()
SELECT employee_id, department_id, salary, rn
FROM (SELECT employee_id, department_id, salary,
RANK () OVER (PARTITION BY department_id ORDER BY salary DESC)
rn
FROM employees)
WHERE rn <= 2
EMPLOYEE_ID DEPARTMENT_ID SALARY RN
200 10 4400 1
201 20 13000 1
202 20 6000 2
114 30 11000 1
115 30 3100 2
203 40 6500 1
...
100 90 24000 1
101 90 17000 2
102 90 17000 2
The salaries that are equal (department 90) receive the same rank