Get second highest salary, return null if no second highest

user_8833502 0
Viewed 2338

Null must be returned if:
- There are less than 2 rows in table
- There isn't a second highest salary because everyone has the same salary

Everything I look up seems to be aimed towards older versions of Sql Server

DECLARE @find int = (Select COUNT(*) from Employee);

IF (@find = 1)
    select null as 'SecondHighest'
else
    select Salary as 'SecondHighest' 
    from Employee
    order by Salary desc
    offset 1 rows fetch next 1 rows only;

I have managed to return null if there is less than 2 columns, although I think there may be a better way to do this. However, I can't fix the second problem:
"There isn't a second highest salary because everyone has the same salary"

tsql sql-server sql
2 Answers

Here's a similar approach to @alx (which was nice and simple btw, so +1) but it will be easier to generalise to pick the nth highest salary:

SELECT MAX(Salary)
FROM
(
  SELECT Salary, DENSE_RANK() OVER(ORDER BY Salary DESC) AS Rank
  FROM Employee
) E
WHERE (Rank = 2);
user_874270

Look at this approach: http://sqlfiddle.com/#!9/dfb2cf/3

Here is the query:

select max(Salary) from Employee where Salary < (select max(Salary) from Employee);

And here is the data:

create table Employee (Id int, Salary int);
insert into Employee values
(1, 100),
(2, 200),
(3, 300);

The query outputs 200 which is the correct answer. I guess it applies to SQL server too.

For edge case with one record it outputs null, i.e. single row with null inside, not a zero rows result.

user_10746210
Related