MySQL query for Getting Second Highest Salary from Employee table in MySQL

“Most Common Question in Interview, how to get 2nd or Nth highest salary from Employee table.”

Now a days i am conducting PHP / Mysql interview for my organisation. And came across many of candidates resumes with 3-4 years of experience in web development. But only couple of developers given me the correct answer. That is what i think, I should share this.

You can write this query in number of ways, but i will explain one of the beset MySQL query which is easy to write and understand. Mysql having advantage of writing query with "Group BY" , “limit” and offset by which you can easily remove dupicate records and handle start and end point.

Lets Start with SQL Schema. Here is the Employee table which have employee_id, employee_name and employee_salary column.

For example, given the above employee table, the second highest salary is 30000. If there is no second highest salary, then the query should return NULL. You can write your SQL query in any of your favorite database specific feature e.g. TOP, LIMIT, OFFSET, GROUP BY, GROUP_CONCAT or ROW_NUMBER, but you must also provide a generic solution which should work on all database. In fact, there are several ways to find second highest salary and you must know couple of them at the time of interview. Once you solve the problem, Interviewer will most likely increase the difficulty level by either moving to Nth salary direction or taking away this buit-in utilities.

Let's First Start With Without LIMIT Clause.

Second Highest Salary in MySQL without LIMIT

Here is a generic SQL query to find second highest employee_salary, which will also work fine in MySQL. This solution uses subquery to first exclude the maximum employee_salary from the data set and then again finds maximum employee_salary, which is effectively find the second maximum employee_salary from the employee table.

SELECT MAX(employee_salary) FROM employee WHERE employee_salary NOT IN ( SELECT Max(employee_salary)
FROM employee);

This will return 30000 in our case.

Here is another solution which uses sub query but instead of IN clause it uses < operator

SELECT MAX(employee_salary) FROM employee WHERE employee_salary < ( SELECT Max(employee_salary)
FROM employee);

You can use this SQL query if Interviewer ask you to get second highest salary in MySQL without using LIMIT.

Second Highest Salary using Correlated SubQuery

Previous SQL query was also using subquery but it was non-correlated, this solution will use correlated subquery. This is also generic solution to find Nth highest salary in Employee table. For each record processed by outer query, inner query will be executed and will return how many records has records has salary less than the current salary. If you are looking for second highest salary then your query will stop as soon as inner query will return 2.

SELECT employee_id, employee_salary, employee_name FROM employee AS e  
WHERE 2=(SELECT COUNT(DISTINCT employee_salary) FROM employee AS p  
WHERE e.employee_salary<=p.employee_salary);

Second Maximum Salary in MySQL using LIMIT

MySQL has a special keyword called LIMIT which can be used to limit the result set e.g. it will allow you to see first few rows, last few rows or range of rows. You can use this keyword to find the second, third or Nth highest salary. Just use order by clause to sort the result.

One of the most common answer which i found in most of the Junior or Senior Developer while taking Interview is below:

SELECT employee_salary FROM employee ORDER BY employee_salary DESC LIMIT 1,1;

Above query will not work, if we have same salary more than one times.

That's why we have to use "DISTINCT" keyword as per below:

SELECT DISTINCT(employee_salary) FROM `employee` ORDER BY employee_salary DESC LIMIT 1,1;

Or you can also use GROUP BY keyword for this as below:

SELECT employee_salary FROM `employee` GROUP BY employee_salary ORDER BY employee_salary DESC LIMIT 1,1

Note that: A DISTINCT and GROUP BY generate the same query result, so performance should be the same across both query constructs. GROUP BY should be used if you are using aggregate fnctions (MAX, SUM, GROUP_CONCAT, ..., or a HAVING clause) to each group. If all you need is to remove duplicates then use DISTINCT.

And Now what if you want to count only unique records, then use below query:

SELECT count(employee_salary) AS unique_count FROM ( SELECT employee_salary FROM employee  GROUP BY employee_salary ORDER BY employee_salary DESC ) AS t

And Now what if you want to count only duplicate no. of records, then use below query:

SELECT count(employee_salary) AS duplicate_count
 FROM (
 SELECT employee_salary FROM employee
 GROUP BY employee_salary HAVING COUNT(employee_salary) > 1
 ) AS t

And Now what if you want to count total no. of times each salary repeated (duplicated), then use below query:

SELECT count(employee_salary) AS total_count, employee_salary FROM employee GROUP BY employee_salary

And now finally the real magic comes here. if you want to count total no. of times each salary repeated (duplicated) with employee_name for that salary, then use below query:

SELECT count(employee_salary) AS total_count, GROUP_CONCAT(employee_name) AS total_employee, employee_salary FROM employee GROUP BY employee_salary ORDER BY employee_salary DESC

And may be your final interview question would be "count total no. of times each salary repeated (duplicated) with employee_name for that salary for particular salary range, then use below query:

SELECT count(employee_salary) AS total_count, GROUP_CONCAT(employee_name) AS total_employee, employee_salary FROM employee GROUP BY employee_salary HAVING (employee_salary > 10000 AND employee_salary < 30000) ORDER BY employee_salary DESC