GuilinDev

Lc0176

05 August 2008

176 Second Highest Salary

Write a SQL query to get the second highest salary from the 

1

Employee
table.

1
2
3
4
5
6
7

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

For example, given the above Employee table, the query should return 

1

200
as the second highest salary. If there is no second highest salary, then the query should return 
1

null
.

1
2
3
4
5

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

Schema

1
2
3
4
5

Create table If Not Exists Employee (Id int, Salary int)
Truncate table Employee
insert into Employee (Id, Salary) values ('1', '100')
insert into Employee (Id, Salary) values ('2', '200')
insert into Employee (Id, Salary) values ('3', '300')

Solution

1
2
3
4
5

# Write your MySQL query statement below
# if need any 3rd or 4th or Nth value, can increase the first value followed by 
# LIMIT (n-1) ie. for 4th salary : LIMIT 3, 1;
select distinct(salary) as SecondHighestSalary from employee 
order by salary desc limit 1, 1

1
2

select max(salary) as SecondHighestSalary from employee 
where salary < (select max(salary) from employee)
This site is open source. Improve this page.