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

Mysql – 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

Tags:

3

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
  3. 0

    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
  )
)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search