'Second Highest Salary

Write a SQL query to get the second highest salary from the Employee table.

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

For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.

    | SecondHighestSalary |
    | 200                 |

This is a question from Leetcode, for which I entered the following code:

    SELECT CASE WHEN Salary = '' 
                THEN NULL
    ELSE Salary 
    END AS SecondHighestSalary 
    FROM (SELECT TOP 2 Salary
                ,ROW_NUMBER() OVER (ORDER BY Salary DESC) AS Num
          FROM Employee
          ORDER BY Salary DESC) AS T
    WHERE T.Num = 2

It says that the query does not return NULL if there's no value for second highest salary. For eg. if the table is

   | Id | Salary| 
   | 1  |  100  |

The query should return

   |SecondHighestSalary|
   |       null        |

and not

   |SecondHighestSalary|
   |                   |

Solution 1:^[1]

In case of ties you want the second highest distinct value. E.g. for values 100, 200, 300, 300, you want 200.

So get the highest value (MAX(salary) => 300) and then get the highest value less than that:

select max(salary) from mytable where salary < (select max(salary) from mytable);

Solution 2:^[2]

Using window functions, utilizing NTH_VALUE gives a clean answer

SELECT ( 
SELECT NTH_VALUE(Salary, 2) OVER(ORDER BY Salary DESC) AS SecondHighestSalary
FROM Employee
GROUP BY Salary
LIMIT 1 OFFSET 1 ) AS SecondHighestSalary
;

Detailed Break-Down:

The outer SELECT Statement is required to get NULL value incase a value was not found (second rank is not present for example, or table only has one row)
NTH_VALUE(Salary, 2) is basically saying, look at each group (in our case it divides the table based on groups of Salary) and for each group add a column that lists the second highest value, for every row within the same group from that new column, we want to pick the second most paid (so only second row)
NTH_VALUE() OVER(ORDER BY) is in ASC order by default, make sure you explicit the DESC order
NTH_VALUE() merely gives the order to the rows within each group (here Salary) incase of two similar salaries in the same salary group it will give them separate ranks (ex 1 and 2) even if they have same value in the same group, For this use GROUP BY () statement
Because NTH_VALUE() merely gives the order to the columns, based on a group USE LIMIT 1 to get just one value (top value) and OFFSET 1 (to make that top value our targeted second most paid)

Solution 3:^[3]

Solution to the Leetcode 2nd highest salary problem is:

Select 
    Max(Salary) AS SecondHighestSalary 
from Employee 
    where Salary < (
                     Select Max(Salary) from Employee
                   );

Solution 4:^[4]

you should be able to do that with OFFSET 1/FETCH 1:

https://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx

Solution 5:^[5]

SELECT id, MAX(salary) AS salary 
FROM employee 
WHERE salary IN
(SELECT salary FROM employee MINUS SELECT MAX(salary) 
FROM employee);

You can try above code to find 2nd maximum salary. The above code uses MINUS operator. For further reference use the below links https://www.techonthenet.com/sql/minus.php https://www.geeksforgeeks.org/sql-query-to-find-second-largest-salary/

Solution 6:^[6]

You can use RANK() function to rank the values for Salary column, along with a CASE statement for returning NULL.

SELECT 
CASE WHEN MAX(SalaryRank) = 1 THEN NULL ELSE Salary as SecondHighestSalary
FROM
(
 SELECT *, RANK()OVER(ORDER BY Salary DESC) As SalaryRank
 FROM Employee 
) AS Tab
WHERE SalaryRank = 2

It would be better to use the DENSE_RANK() function so that ranks don't get skipped whenever there is a tie for a position.

Solution 7:^[7]

I would use DENSE_RANK() & do LEFT JOIN with employee table :

SELECT t.Seq, e.*
FROM ( VALUES (2) 
     ) t (Seq) LEFT JOIN
     (SELECT e.*,
             DENSE_RANK() OVER (ORDER BY Salary DESC) AS Num
      FROM Employee e
     ) e 
     ON e.Num = t.Seq;

Solution 8:^[8]

While you can use a CTE (from MSSQL 2005 or newer) or ROWNUMBER the easiest and more "portable" way is to just order by twice using a subquery.

select top 1 x.* from
(select top 2 t1.* from dbo.Employee t1 order by t1.Salary) as x
order by x.Salary desc

The requisite to show null when there's not a second bigger salary is a bit more tricky but also easy to do with a if.

if (select count(*) from dbo.Employee) > 1
begin
    select top 1 x.* from
    (select top 2 emp.* from dbo.Employee emp order by emp.Salary) as x
    order by x.Salary desc
end
else begin
    select null as Id, null as Salary
end

Obs:. OP don't said what to do when the second largest is a tie with the first but using this solution is a simple matter of using a DISTINCT in the IF subquery.

Solution 9:^[9]

Here is the easy way to do this

SELECT MAX(Salary) FROM table WHERE Salary NOT IN (SELECT MAX(Salary) FROM table);

Solution 10:^[10]

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)

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

drop table #salary

The output is here 200 as 300 is first highest, 200 is second highest and 100 is the third highest as shown below

salary
200

Here n is 2

Solution 11:^[11]

Query:

CREATE TABLE a
    ([Id] int, [Salary] int)
;
     
INSERT INTO a
    ([Id], [Salary])
VALUES
    (1, 100),
    (2, 200),
    (3, 300)
;

GO
SELECT Salary as SecondHighestSalary
FROM a 
ORDER BY Salary 
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY

| SecondHighestSalary |
| ------------------: |
|                 200 |

Solution 12:^[12]

select case 
when cnt>1 then SecondHighestSalary
else null end as SecondHighestSalary
from 
(select top 1 Salary as SecondHighestSalary,
(select count(distinct Salary)  from Employee) as cnt 
from (
select distinct top 2 Salary 
from Employee
order by Salary desc ) as sal
order by SecondHighestSalary asc) as b

Solution 13:^[13]

Select salary from employees limit 1,1 ;

Very easy way to find second highest salary

Solution 14:^[14]

select
  case when max(salary) is null then null else max(salary) end SecondHighestSalary 
from (
  select salary , dense_rank() over (order by salary desc) as rn
  from Employee
)r
where rn = 2

This code returns null when there is no second highest salary

Solution 15:^[15]

You can use the union condition to handle the null case

SELECT Salary as "SecondHighestSalary" from Employee 
WHERE Salary < (SELECT MAX(salary) FROM Employee )    
UNION
(SELECT null)
ORDER BY 1 DESC
LIMIT 1;

Solution 16:^[16]

You can use exists() together with If-else statements.

Query:

If exists(select distinct salary as SecondHighestSalary from employee
order by salary desc offset 1 row fetch first 1 row only) 

select distinct salary as SecondHighestSalary from employee
order by salary desc offset 1 row fetch first 1 row only

else select null as SecondHighestSalary;

Solution 17:^[17]

#Please check the below code#

SELECT TOP 1 secondhighestsalary 
  FROM (SELECT  
        CASE WHEN z.SalaryRank >1 THEN Salary 
        ELSE null END secondhighestsalary, SalaryRank 
        FROM 
(SELECT salary, RANK() OVER(ORDER BY Salary DESC) As SalaryRank 
FROM Employee GROUP  BY salary )z 
GROUP BY Salary, SalaryRank 
)c 
ORDER BY secondhighestsalary DESC

Explanation:

** SELECT salary, RANK() OVER(ORDER BY Salary DESC) As SalaryRank FROM Employee GROUP BY salary**

above query is to provide rank to Salary column, group by clause will take care the duplicate values ..... next

**SELECT
CASE WHEN z.SalaryRank >1 THEN Salary ELSE null END secondhighestsalary, SalaryRank FROM (SELECT salary, RANK() OVER(ORDER BY Salary DESC) As SalaryRank FROM Employee GROUP BY salary )z **

next piece of code assign NULL against rank 1 and salary for greater than 1 rank.

So if you have only one row in the table and as per our question we need to display second highest salary if not display NULL, it will take care that situation.

At last we need to Order by DESC and take the Top 1 record.

Solution 18:^[18]

SELECT max(Salary) as SecondHighestSalary from Employee where salary <>(SELECT max(salary) from Employee)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Thorsten Kettner
Solution 2	Paul Roub
Solution 3
Solution 4	Z.D.
Solution 5	Balakrishnan Bsk
Solution 6	Strange Coder
Solution 7	Yogesh Sharma
Solution 8
Solution 9	Omar
Solution 10	Suraj Kumar
Solution 11	Mark Rotteveel
Solution 12	shivendra
Solution 13	Noobie _here
Solution 14	Nico Albers
Solution 15	Josef
Solution 16	user8745260
Solution 17
Solution 18	Somnath More

'Second Highest Salary

Solution 1:[1]

Solution 2:[2]

Detailed Break-Down:

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]

Solution 6:[6]

Solution 7:[7]

Solution 8:[8]

Solution 9:[9]

Solution 10:[10]

Solution 11:[11]

Solution 12:[12]

Solution 13:[13]