그룹별 순위 가져오기

가져오는 그룹 컬럼이 1개일떄

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
mysql 5.7
SELECT name, year, month FROM (
  SELECT *, IF(@prev <> name, @rn:=0,@rn), @prev:=name, @rn:=@rn+1 AS rn
  FROM example, (SELECT @rn:=0) rn, (SELECT @prev:='') prev
  ORDER BY name ASC, year DESC, month DESC
) t WHERE rn <= 2;

mysql 8.0 
SELECT name, year, month FROM (
  SELECT name, year, month, 
    ROW_NUMBER() OVER (PARTITION BY name ORDER BY year DESC, month DESC) AS rn
  FROM example
) t WHERE rn <= 2;

가져오는 그룹 컬럼이 1개일떄

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
mysql 5.7
SELECT name, year, month FROM (
    SELECT *, IF(@prev <> name + year, @rn:=0,@rn), @prev:=name + year, @rn:=@rn+1 AS rn
    FROM example, (SELECT @rn:=0)rn, (SELECT @prev:='')prev
    ORDER BY name ASC, year DESC, month DESC
)t WHERE rn <= 2;
Using MySQL 8.0 with ROW_NUMBER:

mysql 8.0
SELECT name, year, month FROM (
  SELECT name, year, month, ROW_NUMBER() OVER (PARTITION BY name, year ORDER BY year DESC, month DESC) AS rn
  FROM example

https://stackoverflow.com/questions/58645949/emulating-partition-over-with-mysql-5-7

#Database
#Query