How to get second largest salary from employee table?

Here I am going to narrate about one of frequent interview question. Get second largest salary from employee table.

I am planning to explain this topic with 6 steps:

Reader must familiar with Mysql database. Otherwise directly look the step 4 & 5 to know about the logic.

1. Software:

Database: Mysql 5.1

Query Browser: mysql command Line Client (come with mysql setup installation)

2. Database & Table ( DDL)

Database: test (Mysql in-build database so no need the query for database creation).

Table Name:employees

Query:

create table employees(emp_no integer primary key,name varchar(100),salary double);

3. Insert records into the employees table. (DML)

Query:

· insert into employess values(1,'Muthu',20000.00);

· insert into employees values(2,'Raj',18000.00);

· insert into employees values(2,'Jack',18000.00);

· insert into employees values(4,'Bala',16000.00);

Summary

mysql> select *from employees;

+--------+-------+--------+

| emp_no | name | salary |

+--------+-------+--------+

| 1 | Muthu | 20000 |

| 2 | Raj | 18000 |

| 3 | Jack | 18000 |

| 4 | Bala | 16000 |

+--------+-------+--------+

4. Answer 1:

select max(salary) from employees where salary not in(select max(salary) from employees);

This query is portable across the all other databases.

Result:

| max(salary) |

+-------------+

| 18000 |

+-------------+

Explanation:

· First execute the where condition select query (sub query) ie. Query become

select max(salary) from employees where salary not in(20000)

· Next execute the above formed query with “not in key word”.

· Not in key word exclude the ‘20000’ salary from table and select second largest query.

5. If they ask to gets the number of employees getting the second largest salary than what will be the query.

Answer:

You have to make one more sub query with “in key word”

Select count(*) from employees where salary in(select max(salary) from employees where salary not in(select max(salary) from employees));

Now you will get 2 as answer.

+----------+

| count(*) |

+----------+

| 2 |

+----------+

6. other way to get second largest salary is:

Query is::

select salary,emp_no,name from employees order by salary desc limit 1,1;

This query only work with mysql but logic is very simple

Make the descending order of the table and select the second record from table.

Descending Order: order by salary desc

Select Second record: limit 1,1;

Using this we can get the remaining details of the employees.

+--------+--------+------+

| salary | emp_no | name |

+--------+--------+------+

| 18000 | 2 | Raj |

+--------+--------+------+

I going to give the option to reader to find the how many employees are getting second largest salary using 6^th step.