CODEWITHSUNDEEP
mysql
Jobz
Jobz

Reputation: 462

MYSQL get all user rows having second max salary

I found many questions for finding - second max salary for employees. But in all these posts, only salary is selecting.

select max(salary) from user
WHERE salary NOT IN (SELECT max(salary) from user)

I need all users rows having second max salary.

sample user table

id       name       salary
------------------------------------
1         A             100
2        B              200
3        C              50
4        D              200
5        E              100

and my expected result is,

id       name       salary
------------------------------------
1        A             100
5        E             100

Upvotes: 1

Views: 333

Answers (3)

sumit
sumit

Reputation: 15464

You can do like below

SELECT * 
FROM   user 
WHERE  salary = (SELECT salary 
                 FROM   user 
                 GROUP  BY salary 
                 ORDER  BY salary DESC 
                 LIMIT  1, 1)

Upvotes: 1

ashir haroon
ashir haroon

Reputation: 243

you will get 2nd max salary by this query

SELECT salary FROM user ORDER BY salary DESC LIMIT 1, 1;

Upvotes: -3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

You could use two subqueries to isolate the second highest salary, e.g.

SELECT id, name, salary
FROM user
WHERE salary = (SELECT MAX(salary) FROM user WHERE salary < (SELECT MAX(salary) FROM user));

enter image description here

Demo

Note that if you are using MySQL 8+, and are open to using analytic functions, then DENSE_RANK really helps here:

WITH cte AS (
    SELECT id, name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) dr
    FROM user
)

SELECT id, name, salary
FROM cte
WHERE dr = 2;

Upvotes: 3

Related Questions