トップ > SQL > Mysql で特定id毎の上位ランキングを出す

Mysql で特定id毎の上位ランキングを出す

SQL

以下は結構便利。

SELECT *
FROM Table1 t1
WHERE 2 >= (
    SELECT COUNT(*)
    FROM Table1 t2
    WHERE t1.category = t2.category
    AND t2.point >= t1.point
)
ORDER BY category,point desc

select id, category, point, name
from 
(
   select *,
      @rank := if (@category = category, @rank + 1, 1) as rank,
      @category := category as dummy_field
  from Table1, (select @rank := 0, @category := 0) as dummy_table
  order by category, point desc
) as t
where t.rank <= 2
order by category, point desc

teratail.com