phprockers-logo

Get the second highest salary from employee table in mysql


We have get the second, third, fourth etc. hightest salary from the employee table very easily in mysql/sql.

Getting max highest salary:
===================

SELECT max(salary) as salary FROM employee;

Getting N th highest salary syntax:
========================

SELECT salary FROM employee as emp WHERE (n-1) = (SELECT count(*) FROM employee as emp2 WHERE emp2.salary > emp.salary)

Getting 2nd maximum salary:
=====================
Above syntax, subsitute n=2, then we have to get second max salary.

SELECT salary FROM employee as emp WHERE (2-1) = (SELECT count(*) FROM employee as emp2 WHERE emp2.salary > emp.salary)

(OR)

SELECT max(salary) as salary FROM employee WHERE salary < (SELECT max(salary) FROM employee)



(OR)

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


Getting First Three maximum salary:
=======================

SELECT salary FROM employee ORDER BY salary DESC LIMIT 0,3.

This is very useful for retriving the highest three maximum salary from the employee table.

2 comments:

  1. this is the only way..... why your using bla... bla.. way...
    think simple and smart way... hav u used TOP keyword...

    ReplyDelete
  2. Indeed great tutorial, by the You can also use rownum() function this way to find second maximum salary in MySQL and SQ Server 2008

    ReplyDelete