SQL Query to Find Nth Highest Salary

How to find third or nᵗʰ maximum salary from salary table?

Use ROW_NUMBER(if you want a single) or DENSE_RANK(for all related rows):

WITH CTE AS
(
    SELECT EmpID, EmpName, EmpSalary,
           RN = ROW_NUMBER() OVER (ORDER BY EmpSalary DESC)
    FROM dbo.Salary
)
SELECT EmpID, EmpName, EmpSalary
FROM CTE
WHERE RN = @NthRow

SQL query to find Nth highest salary

First, the query will return the nth lowest salary value. To return the nth highest salary value you must change t.sal <= sal to t.sal >= sal.

Next, this query works by first finding the distinct list of salary values as one derived table and then determines the number of employees that have a salary less than each one in this list. t.sal <= sal is taking the derived table (which most databases would require have an alias) and comparing each value against the outer emp table. It should be noted that this will return multiple rows in the case of a tie.

To manually trace the output, we need some inputs:

Alice       | 200
Bob         | 100
Charlie     | 200
Danielle    | 150

Select Distinct sal
From emp

Gives us

200
100
150

Now we analyze each row in the outer table

Alice - There are 3 distinct salary values less than or equal to 200
Bob - 1 rows <= 100
Charlie - 3 rows <= 200
Danielle - 2 row <= 150

Thus, for each salary value we get the following counts (and reordered by count):

Bob 1
Danielle 2
Charlie 3
Alice 3

The most important aspect that I think you are overlooking is that the outer emp table is correlated to the inner count calculation (which is why it is called a correlated subquery). I.e., for each row in the outer emp table, a new count is calculated for that row's salary via t.sal <= sal. Again, most database systems would require the inner most query to have an alias like so (note the As Z alias):

Select sal
From emp As t
Where &n =  (
            Select Count(Z.sal)
            From    (
                    Select Distinct sal
                    From emp
                    ) As Z
            Where t.sal <= Z.sal
            )

find nth highest salary in sql

SELECT * FROM EMPLOYEE e1
WHERE N-1 = 
           (SELECT COUNT(e2.ORIG_SALARY) FROM EMPLOYEE e2
                  WHERE e2.ORIG_SALARY > e1.ORIG_SALARY)

COUNT(e2.ORIG_SALARY) outputs the amount of entries in this column in the returned data set. The data set is all the salaries higher then the one in the row your main query returns.

WHERE N-1 = means that it will get the result where that count matches N-1

So that means you will get the a row of the table employee where there are N-1 higher salaries in the table, effectively giving you the row with the nth highest salary.

Keep in mind however, this is not perfect. For instance if you have multiple people with the same salary in the top 6, you don't get the nth highest salary, you get the nth highest sorted descending by salary.

Nth max salary in Oracle

try this

select *
  from
  (
    select
        sal
          ,dense_rank() over (order by sal desc) ranking
    from   table
  )
  where ranking = 4 -- Replace 4 with any value of N

dense_rank() and max, which one is better to find Nth highest salary (return null if no result)

This is a really interesting question. You haven't specified the database. But if I assume that you have an index on salary that can be used by the query (so possibly salary desc depending on the database). Then your first query might have really good performance:

Fetch the match salary using the index.
Start scanning the index for values less than the max.
Short circuit the scan because you get the max right away.

I'm not promising that all databases would generate this plan, but two index lookups would typically be faster than dense_rank() in this case.

Of course, with any question like this, you should test on your data and your database. That is really the correct answer.

SQL query to find Nth highest salary from a salary table

You can use a Common Table Expression (CTE) to derive the answer.

Let's say you have the following salaries in the table Salaries:

 EmployeeID  Salary
--------------------
     10101   50,000
     90140   35,000
     90151   72,000
     18010   39,000
     92389   80,000

We will use:

DECLARE @N int
SET @N = 3  -- Change the value here to pick a different salary rank

SELECT Salary
FROM (
    SELECT row_number() OVER (ORDER BY Salary DESC) as SalaryRank, Salary
    FROM Salaries
) as SalaryCTE
WHERE SalaryRank = @N

This will create a row number for each row after it has been sorted by the Salary in descending order, then retrieve the third row (which contains the third-highest record).

SQL Fiddle

For those of you who don't want a CTE (or are stuck in SQL 2000):

[Note: this performs noticably worse than the above example; running them side-by-side with an exceution plans shows a query cost of 36% for the CTE and 64% for the subquery]:

SELECT TOP 1 Salary
FROM 
(
    SELECT TOP N Salary
    FROM Salaries
    ORDER BY Salary DESC
) SalarySubquery
ORDER BY Salary ASC

where N is defined by you.

SalarySubquery is the alias I have given to the subquery, or the query that is in parentheses.

What the subquery does is it selects the top N salaries (we'll say 3 in this case), and orders them by the greatest salary.

If we want to see the third-highest salary, the subquery would return:

 Salary
-----------
80,000
72,000
50,000

The outer query then selects the first salary from the subquery, except we're sorting it ascending this time, which sorts from smallest to largest, so 50,000 would be the first record sorted ascending.

As you can see, 50,000 is indeed the third-highest salary in the example.