Odtnhj

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|
 | |

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);

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

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

You can use RANK() function to rank the values for Salary column.

SELECT *
FROM
(
 SELECT *, RANK()OVER(ORDER BY Salary DESC) As SalaryRank
 FROM Employee 
) AS Tab
WHERE SalaryRank = 2

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/

Here is the easy way to do this

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

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;

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.

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 |