Get second highest salary for each person in mysql

Question

We have table like below

person_id  |    salary
   1       |     1500
   1       |     1000
   1       |      500
   2       |     2000
   2       |     1000
   3       |     3000
   3       |     2000
   4       |     3000
   4       |     1000

We want second highest salary for each person. grouping by each person and get second highest salary for person. like below

person_id  |    salary
   1       |     1000
   2       |     1000
   3       |     2000
   4       |     1000

Thanks in advance :)

Answer 1

By using aggregate function and self join you could do something like

select a.*
from demo a
left join demo b on a.person_id = b.person_id
group by a.person_id,a.salary
having sum(a.salary < b.salary) = 1 /* 0 for highest 1 for second highest 2 for third and so on ... */

or using complete case expression in sum

having sum(case when a.salary < b.salary then 1 else 0 end)  = 1

Demo

Note This doesn't handle ties like a person may have 2 same salary values, i assume each salary value for a person will be different from other salary values for a person to handle such case approach mentioned by @juergen d will work with additional case statement

Answer 2

Here is one way using exists and having clause

SELECT person_id,
       Max(salary)
FROM   Yourtable a
WHERE  EXISTS (SELECT 1
               FROM   Yourtable b
               WHERE  a.person_id = b.person_id
               HAVING ( a.salary < Max(b.salary)
                        AND Count(*) > 1 )
                       OR Count(Distinct salary) = 1)
GROUP  BY person_id