Support : team at fbchandra dot com

MySQL

MySQL second highest salary
How to find second highest salary in mysql?

It is the most asked question in interview. Interviewer want your concept of limit and query should be crystal clear. We have explained from creation of table to display table data. At last we have given a simple query to execute second highest salary.

Lets consider you have a table employee.

Structure of employee table is as follows:

create table employee (id int(11) not null auto_increment primary key, name varchar(100), salary varchar(10))

Insert its values using queries

insert into employee VALUES (Null,'Derek','10000'),(Null,'Urmila','20000'),(Null,'Rajni','15000'),(Null,'Sukhbinder','12000'),(Null,'Preeti','14000')

Display data using query

Select * from employee

Its values will be displayed as follows:

id name salary
1 Derek 10000
2 Urmila 20000
3 Rajni 15000
4 Sukhbinder 12000
5 Preeti 14000

We have to find second highest salary here, so our answer should match with name Rajni and Rs. 15000

SQL Query to find second highest salary is as follows

select name, salary from employee order by salary desc limit 1,1

Its output display as follows:

Name Salary
Rajni 15000

Explanation:

  • First select required data from given table - select name, salary from employee
  • Sort data in descending order. Currently after sorting Urmila will come first and Rajni will appear in second position - order by salary desc
  • Use limit to show only second record. - limit 1
  • Display one record at a time - ,1
Back