4

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 :)

Maharjun M
  • 853
  • 4
  • 11
  • 24
  • Related [What is the simplest SQL Query to find the second largest value?](https://stackoverflow.com/questions/32100/what-is-the-simplest-sql-query-to-find-the-second-largest-value) – Tulir Jan 11 '18 at 10:39
  • 1
    Possible duplicate of [SQL select nth member of group](https://stackoverflow.com/questions/463054/sql-select-nth-member-of-group) – Valerica Jan 11 '18 at 10:50

5 Answers5

3

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

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
3

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
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
  • Even this won't return the rows for person_id having same multiple salaries or just one salary. – Ullas Jan 11 '18 at 11:36
  • 1
    No same `person_id` with same salaries in multiple rows having the issue. **[check here pls](http://sqlfiddle.com/#!9/adb1de/3)** – Ullas Jan 11 '18 at 12:10
1

Try

select t1.*
from your_table t1
join
(
    select person_id,
           @rank := case when person_id = @prevPersonId then @rank + 1 else 1 end as rank,
           @prevPersonId := person_id
    from your_table
    cross join (select @rank := 0, @prevPersonId := 0) r
    group by person_id
    order by person_id asc, salary desc
) t2 on t1.person_id = t2.person_id
where rank = 2
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • 1
    Possible duplicate of: https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results ... but +1 anyway :-) – Tim Biegeleisen Jan 11 '18 at 10:46
0

Another way around JOIN.

Query

 select t1.`person_id`, max(coalesce(t2.`salary`, t1.`salary_1`)) as `salary_2` from(
   select `person_id`, max(`salary`) as `salary_1`
   from `your_table_name`
   group by `person_id`
) t1
left join `your_table_name` t2
on t1.`person_id` = t2.`person_id`
and t1.`salary_1` <> t2.`salary`
group by t1.`person_id`;

Find a sql fiddle demo here

Ullas
  • 11,450
  • 4
  • 33
  • 50
0

You could also use

select max(salary), person_id
from (select salary, person_id from demo 
      except 
      (select max(salary), person_id from demo group by person_id)) s
group by person_id

removing max salaries for each person from initial dataset and starting over.

Alexander Prokofyev
  • 33,874
  • 33
  • 95
  • 118