Retrieve 2nd highest count by each group

Question

I have a table like this:

shopID    supplier    supply_count
1         a           12
1         b           10
1         c           8
1         d           7
2         b           12
2         f           12
2         e           10
3         b           5
3         a           2
4         f           15
4         c           11

I used the not in function like this:

where supply_count NOT IN (select max(supply_count) from supply)

However, only the first row show the 2nd highest value in the result, the other rows still showing the highest count:

shopID   supply_count
1        10
2        12
3        5
4        15

My expected result is to find the 2nd highest supply count for each shop like this:

shopID   supply_count
1        10
2        12
3        2
4        11

So, anyone have some suggestions? thanks!

Answer 1

use row_number()

select shopid,supply_count
from
(
select shopID,supply_count,row_number() over(partition by shopID order by supply_count) as rn
from tablename
)A where rn=2 

Answer 2

use row_number if your dbms support

with cte as
(
select *,row_number() over(partition by shopID order by supply_count desc) rn from table_name
) select * from cte where rn=2

Answer 3

Your solution is quite interesting. You just need to finish like this

select s1.shopId, max(s1.supply_count)
from supply s1
where supply_count NOT IN (
   select max(supply_count) 
   from supply s2
   where s1.shopId = s2.shopId
)
group by s1.shopId

This should work on most today database systems (when compared to window functions). However, window functions tend to be a more efficient solution if you will read a significant portion of your tables.

Answer 4

in some cases just ordering and limiting the result can be useful:

SELECT suply_count FROM shop
ORDER BY suply_count DESC limit 1,1;