Question 233568 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Issue with finding second highest salary in sql

I am having a table looks like below.

Table schema

I need second highest salary of all the employess table.

When I do below query I am getting expected result:

Expected result

But it giving me empty result when i do

SELECT id,employee_id,name,salary 
FROM `employess` 
WHERE id not in (
    select id from `employess`
    where 1 
    group by employee_id 
    order by id desc)  
GROUP BY employee_id 
order by salary desc;

Second highest salaries of all the employess.

Tags:

2

Answers


  1. 0

    The subquery returns all employee IDs, so excluding them in the main query excludes everyone.

    Change the subquery so it just returns the highest salary. Then exclude that salary, order by salary, and return the first of that.

    SELECT id,employee_id,name,salary 
FROM `employess` 
WHERE salary != (
    SELECT MAX(salary)
    FROM employess
)
ORDER BY salary DESC
LIMIT 1

    Or return the ID of the employee with the highest salary and exclude them.

    SELECT id,employee_id,name,salary 
FROM `employess` 
WHERE id != (
    SELECT id
    FROM employess
    ORDER BY salary DESC
    LIMIT 1
)
ORDER BY salary DESC
LIMIT 1
    Login or Signup to reply.
  2. 0

    You can utilize the dense_rank function in MySQL to sort employees based on their highest salaries. Then, you filter the results using the where clause to obtain the second-highest salary from employees, as you desire.

    with cte_employees as
(
    select dense_rank() over (partition by employee_id order by salary desc) as rank,
           id,
           employee_id,
           name,
           salary
    from employees
)
select id, employee_id, name, salary
from cte_employees
where rank = 2;

    For further information about dense_rank, you can visit the official MySQL documentation.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search