Home / MySql / How to get second-highest salary of employees in a table

How to get second-highest salary of employees in a table

Method 1)
Below query can be used to find the nth maximum value, just replace 1 from nth number

Select Code
SELECT * FROM `emp` e1 where 1 = (select count(distinct(salary)) from emp e2 where e2.salary>e1.salary)

Method 2)

Select Code
select * from emp where salary = (select max(salary) from emp where salary < (select max(salary) from emp ))

Method 3)
if u want the second minimum then just change the max to min and change the less than(<) sign to grater than(>).

Select Code
select max(salary) from emp where salary<(select max(salary) from emp)

Method 4)
if u want the second minimum then just change the desc to asc.

Select Code
select * from emp order BY Salary desc limit 1,1

Method 5)

Select Code
SELECT * FROM `emp` WHERE salary = ( SELECT salary FROM emp e ORDER BY salary DESC LIMIT 1, 1 )

Method 6)

Select Code
SELECT MAX(Salary) FROM emp WHERE Salary NOT IN (SELECT MAX(Salary) FROM emp)

Method 7)

Select Code
select MAX(salary) from emp where salary!= (select MAX(salary) from emp )

About v.shakya

I am V.Shakya, Software Developer & Consultant I like to share my ideas, views and knowledge to all of you who come across my website. I am young, enthusiastic, highly motivated and self disciplined person. I completed my studies in Master of Computer Application and currently giving my technical expertise to one of the Big IT company. I have more than fifteen years of experience in vast field of Programming , Designing and Development of websites and various software's.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.