By arunraj
Consider the following tables Employee and Department.
CREATE TABLE DEPARTMENT
(
DEPT_ID NUMBER(19) NOT NULL,
DEPT_NAME VARCHAR2(255 CHAR),
BRANCH VARCHAR2(255 CHAR)
)
(
DEPT_ID NUMBER(19) NOT NULL,
DEPT_NAME VARCHAR2(255 CHAR),
BRANCH VARCHAR2(255 CHAR)
)
CREATE TABLE EMPLOYEE
(
EMP_ID NUMBER(19) NOT NULL,
EMP_NAME VARCHAR2(255 CHAR),
AGE NUMBER(10),
SALARY NUMBER(10),
DEPT_ID NUMBER(19)
)
To find the list of employees earning maximum salary in each department use the following query.
SELECT e.dept_id,emp_name, MAX(salary) FROM employee e,department d
WHERE E.DEPT_ID=D.DEPT_ID
GROUP BY e.dept_id,emp_name
WHERE E.DEPT_ID=D.DEPT_ID
GROUP BY e.dept_id,emp_name
To find the list of employees earning nth maximum salary in each department use the following query.
SELECT *
FROM (SELECT dept_id, emp_name, salary,
ROW_NUMBER() OVER ( partition BY dept_id
ORDER BY salary DESC ) rn
FROM employee )
WHERE rn = n
FROM (SELECT dept_id, emp_name, salary,
ROW_NUMBER() OVER ( partition BY dept_id
ORDER BY salary DESC ) rn
FROM employee )
WHERE rn = n
In the above query substitute a value for n to find the nth max salary.