CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
SukuAD
SukuAD

Reputation: 53

To find the Nth Highest Salary

Write a SQL query to get the nth highest salary from the Employee table (SQL Server)

| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |

For this example, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.

| getNthHighestSalary(2) |
+------------------------+
| 200                    |

Is there any way to write this query other than by using function?

Upvotes: 3

Views: 4470

Answers (11)

monir_sec
monir_sec

Reputation: 21

Another Possible approach.I think this could help .

select min(T.Salary) from
(
select distinct top n  Salary from Employee order by Salary desc
)T

Upvotes: 0

UsamaDev
UsamaDev

Reputation: 17

The best approach

select salary from emp as e1 where N-1 (select count(distinct salary) from emp as e2 where e2.salary > e1.salary);

Upvotes: 0

Sharad Chavhan
Sharad Chavhan

Reputation: 1

SELECT  DISTINCT(Salary) FROM Employee ORDER BY Salary DESC LIMIT n-1,1;

Upvotes: 0

Gul Akkoc
Gul Akkoc

Reputation: 21

177. Nth Highest Salary - Leetcode

Try this out ! It worked !

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN 
SET N = N-1;
    RETURN (
    SELECT DISTINCT Salary as "getNthHighestSalary(2)"
    FROM Employee
    ORDER BY Salary DESC
    LIMIT 1 OFFSET N
  
  );
END

Upvotes: 2

Suvorno Mukherjee
Suvorno Mukherjee

Reputation: 1

I tried this query and it worked

select sal from (select sal from employee
order by sal desc
limit n)
order by sal asc
limit 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

The following does almost exactly what you want:

select salary
from employee
order by salary desc
offset <n> rows fetch next 1 row only;

The only problem is that it does not return NULL when there is no such salary. You can handle using a subquery:

select (select salary
        from employee
        order by salary desc
        offset <n> rows fetch next 1 row only
       ) as salary;

If you want ties to have the same ranking, then use select distinct salary in the subquery.

Upvotes: 5

Gowtham
Gowtham

Reputation: 11

You can simply try this using correlated sub-query to find Nth highest salary in Employee table..

SELECT *
FROM Employee Emp1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)

Upvotes: 1

Zhorov
Zhorov

Reputation: 29943

Another possible approach is using ROW_NUMBER() or DENSE_RANK() functions. It is important to know if there can be more then one salary at or before Nth position.

CREATE TABLE #Salary (
    [id] int,
    [salary] numeric(10, 2)
)

INSERT #Salary 
    ([id], [salary])
VALUES
    (1, 100),
    (2, 500),
    (3, 200),
    (4, 300),
    (5, 200);

DECLARE @Position int = 2;

-- With possible duplicate salaries
WITH cte AS (
    SELECT 
        [id], [salary],
        DENSE_RANK() OVER (ORDER BY [salary] ASC) AS [DRPosition]
    FROM #Salary
)
SELECT [id]
FROM cte
WHERE [DRPosition] = @Position
ORDER BY [DRPosition];

-- Without possible duplicate salaries
WITH cte AS (
    SELECT 
        [id], [salary],
        ROW_NUMBER() OVER (ORDER BY [salary] ASC) AS [RPosition]
    FROM #Salary
)
SELECT [id]
FROM cte
WHERE [RPosition] = @Position
ORDER BY [RPosition]

Upvotes: 6

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can try this for getting n-th highest salary, where n = 1,2,3....(int)

SELECT TOP 1 salary FROM (
   SELECT TOP n salary 
   FROM employees 
   ORDER BY salary DESC) AS emp 
ORDER BY salary ASC

Hope this will help you. Below is one of the implementation.

create table #salary (salary int)
insert into #salary values (100), (200), (300), (400), (500)

SELECT TOP 1 salary FROM (
   SELECT TOP 3 salary 
   FROM #salary 
   ORDER BY salary DESC) AS emp 
ORDER BY salary ASC

drop table #salary

The output is here 300 as 500 is first highest, 400 is second highest and 300 is the third highest as shown below

salary
300

Here n is 3

Upvotes: 2

forpas
forpas

Reputation: 164089

Finds the salary for which there exist exactly 8 higher salaries,
meaning finds the 9th highest salary.
Returns NULL if there is no 9th Salary:

SELECT DISTINCT e.Salary FROM Employee e WHERE
(SELECT COUNT(*) FROM Employee ie WHERE ie.Salary > e.Salary) = 8
UNION 
SELECT NULL WHERE (SELECT COUNT(Salary) FROM Employee) < 9

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

You can try this using row_number()

WITH CTE AS
(
    SELECT EmpID, Salary,
           RN = ROW_NUMBER() OVER (ORDER BY Salary DESC)
    FROM Employee
)
SELECT EmpID, Salary
FROM CTE
WHERE RN = n

Upvotes: 1

Related Questions