1

In a given question,

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

I wrote a code for this question, however, the expected output is different.

My Code:

SELECT
CASE
    WHEN COUNT(*) = 1 THEN NULL
    ELSE (SELECT Salary FROM Employee HAVING Salary < MAX(Salary) ORDER BY Salary DESC LIMIT 1)
END AS SecondHighestSalary
FROM Employee;

I think there's something wrong with my code but I cannot find what is the actual problem here. My code returns 100 not 200. What have I gotten wrong?

+---------------------+
| SecondHighestSalary |
+---------------------+
| 100                 |
+---------------------+
Strawberry
  • 33,750
  • 13
  • 40
  • 57
Seungho Lee
  • 1,068
  • 4
  • 16
  • 42

5 Answers5

3

You can utilize LIMIT {[offset,] row_count}. Refer https://dev.mysql.com/doc/refman/8.0/en/select.html

Order by Salary in descending order, and get the second row by defining OFFSET as 1. We will use DISTINCT on Salary as there is a possibility to have multiple rows for the highest salary.

SELECT DISTINCT
  Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1,1
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
  • @Sadikhasan how does it even matter, as OP only wants the salary figure, not the full row. – Madhur Bhaiya Nov 21 '18 at 06:56
  • Thank you for your answer. But I tried this and this does not return `null` when there's only one employee. I think maybe adding `IFNULL()` would do? – Seungho Lee Nov 21 '18 at 07:20
  • @Poream3387 hmm.. let me test. I havent tested it. – Madhur Bhaiya Nov 21 '18 at 07:23
  • @Poream3387 it returns no results: https://www.db-fiddle.com/f/e1Efjj6SY5xVrTEuJ9oP4j/0 - It is pretty much equivalent to getting `null`. You can handle this in application code (eg: PHP), when no rows are returned. – Madhur Bhaiya Nov 21 '18 at 07:26
  • By wrapping the answer in a select you would be able to get "null" value if there is no second highest salary – George Joseph Nov 21 '18 at 08:37
  • @GeorgeJoseph it is unnecessary, when such basic things can be handled more efficiently in the application code. Why unnecessarily burden the MySQL server with a materialized subquery. – Madhur Bhaiya Nov 21 '18 at 08:50
  • @MadhurBhaiya i agree with you on the burden part and also disagree--> nulls and no_rows_returned are not the same thing – George Joseph Nov 21 '18 at 08:55
2

You can try below

SELECT MAX(salary) From Employee WHERE salary < ( SELECT Max(salary) FROM Employee);
Fahmi
  • 37,315
  • 5
  • 22
  • 31
1

Try that out:

SELECT salary FROM Employee ORDER BY salary DESC LIMIT 1,1

or to a deeper approach you can use something like:

SELECT salary FROM Employee GROUP BY salary ORDER BY salary DESC LIMIT 1,1

All that queries have high performance, since they does not have any subqueries.

rod.dinis
  • 1,233
  • 2
  • 11
  • 20
0

If you want to display a empty_row(null) in case there is no highest salary then the following does it, if there is a value then it gets shown

select (select salary
          from Employee ORDER BY salary DESC LIMIT 1,1
        ) as x
George Joseph
  • 5,842
  • 10
  • 24
0

Here is the solution.

SELECT MAX(salary) From Employee WHERE salary < ( SELECT Max(salary) FROM Employee);
Sahil Anand
  • 139
  • 5