I want to make a query for this below statement:
find the ninth employee 9th highest salary
I found below query from google
SELECT * FROM EMPLOYEE e1
WHERE N-1 =
(SELECT COUNT(e2.ORIG_SALARY) FROM EMPLOYEE e2
WHERE e2.ORIG_SALARY > e1.ORIG_SALARY)
in this query what is functioning role of " e1, e2 & N-1 "
please help to describe this.
Thanks in advance
e1,e2 are aliases . N is numerical others. In you case is 9.
This query has a correlated subquery. For each row from table employee, it calculates the count of the employee with the higher salary. Why ( N-1 , 9-1= 8) because If you find 8 employees with the higher salary. Probably current row has 9th salary.
But for me, this solution is not clear. I prefer analytic function for this.
Select * from (
SELECT e1.*, DENSE_RANK() OVER ( ORDER BY e1.sal) rn FROM emp e1) where rn =9;
Select * from (
SELECT e1.*, row_number() OVER ( ORDER BY e1.sal) rn FROM emp e1) where rn = 9;
