All Forums Database
KVB 124 posts Joined 09/12
08 Oct 2013
How to find a third lowest salary without RANK or TOP function

I need to find the third lowest salary without using RANK or TOP function?

M.Saeed Khurram 544 posts Joined 09/12
08 Oct 2013

You can find it using the following co-related subquery:

SELECT * FROM Employee E1 WHERE n-1 = 
(SELECT COUNT(DISTINCT Salary_amount) FROM Employee E2 WHERE E1.Salary_amount > E2.Salary_Amount)

Where n is the level you want to find. for example in your case, replace n with 3.


KVB 124 posts Joined 09/12
09 Oct 2013

Yes.Got it.Thank you.

LUCAS 56 posts Joined 06/09
16 Oct 2013

did you try with a window function ?
-- Partition by Department
qualify row_number() over(partition by Department order by Salary_Amount) < n
-- NO Partition at all
qualify row_number() over(order by Salary_Amount) < n

You must sign in to leave a comment.