Query to find least fifth salaried employee

Question

EMPID NAME DEPTID SALARY
---------- ------------------------------------------ --
101 surendra 201 1000
102 narendra 202 2000
103 rajesh 203 3000
104 ramesh 203 2000
105 hanumanth 202 10000

a) Write a Query to find least 5th (Least salary in 5th position from least salary in the order) salaried employee?

b) Query to find the highest earning employee in each department

Answer 1

a) Write a Query to find least 5th (Least salary in 5th position from least salary in the order) salaried employee?

SELECT EMPID,NAME,DEPTID,SALARY
FROM
(
SELECT EMPID,NAME,DEPTID,SALARY, DENSE_RANK() OVER (ORDER BY SALARY) AS RN
FROM Table1
) 
WHERE RN=5

b) Query to find the highest earning employee in each department

SELECT EMPID,NAME,DEPTID,SALARY
FROM
(
SELECT EMPID,NAME,DEPTID,SALARY, 
  DENSE_RANK() OVER (PARTITION BY DEPTID ORDER BY SALARY DESC) AS RN
FROM Table1
)
WHERE RN=1

Demo

http://sqlfiddle.com/#!4/63ce0/12