Get second highest salary in SQL

Question

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

Answer 1

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.

Answer 2

I don’t think your SQL is going to give you the result that you want but that’s a secondary issue. To solve the error just increase the timeout setting: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_net_read_timeout

Answer 3

It's a little convoluted, but you can avoid an order by like this:

select emp_no, salary
from salaries
where salary = (
  select max(salary)
  from salaries
  where salary != (
    select max(salary)
    from salaries
  )
)

Answer 4

Yet another option using LIMIT + OFFSET keyword:

SELECT emp_no,
       salary
FROM salaries
ORDER BY salary DESC
LIMIT 1 OFFSET 1

If you can have more than two records with the 2nd highest salary, Joel's answer is more suitable, yet changing window function to DENSE_RANK instead of ROW_NUMBER.