Search code examples
sqlmysqlcorrelated-subquery

Get second highest salary in SQL

Getting Connection Lost in coorelated queries.

select emp_no,salary
from salaries e 
where 2 = 
    (select count(distinct salary) from salaries p where p.salary>e.salary);

I tried this to get second highest salary but its giving me sql connection lost every time. All other queries are working fine except this one.

I m using sql workbench.

Attached: Screenshot of error

Solution

  • It looks like the connection has a 30 second time out, and the inefficient query is taking too long. This way is much faster, and should finish before the timeout:

    SELECT emp_no, salary
FROM
(
    select emp_no,salary,row_number() over (order by salary desc) rn
    from salaries e  
) t
WHERE rn = 2

    Eventually, if MySQL properly implements the ANSI QUALIFY keyword this might need even less code and run even a little faster.