Mysql Query : Nth highest salary from employee table

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

Now a days i am conducting PHP / Mysql interview for my organisation. And came across many of 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 query in number of ways, but i list few of them which are easy to write and understand. Mysql having advantage of writing query with "limit", by which you can handle start and end point.

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

This query will work only in MySql as it uses "limit".

For other Sql Database you can opt for following query:

SELECT MAX(salary) FROM employee WHERE salary NOT IN (SELECT MAX(salary) FROM employee)

OR

SELECT DISTINCT(a.salary) FROM employee a WHERE 2 = (SELECT COUNT( DISTINCT(b.salary) )FROM employee b WHERE a.salary <= b.salary ) ;

Last query will retrieve N number of highest salary from employee table. just you need to change "2" as per your requirement.

Enjoy Exploring Mysql......:)