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, Freelance PHP Programmer. 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 Ten 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.

Please fill in the form and submit to subscribe

Welcome to VShakya

For this week my all price is very low !

Don't miss to get my services

Thanks

 

Contact us