[MySQL] Group별로 특정 컬럼이 가장 큰 레코드를 추출하는 쿼리

Oracle 에서는 rank(), partition by 등의 분석 함수를 써서 Group 내의 특정 컬럼의 랭킹을 쉽게 구할 수 있는데, MySQL 에서는 이렇게 하려면 좀 까다롭다. Googling 해 보니 여러가지 방법이 있는데, 가장 간단한 방법은 group_concat Function을 사용하는 것 같다.

-- PROJECT_ID 별로 HISTORY_NO 값이 가장 큰 레코드를 추출하는 쿼리.

SELECT M.PROJECT_ID, E.* 

FROM ORDER_SHEET E JOIN (

SELECT

K.PROJECT_ID, 

SUBSTRING_INDEX ( group_concat(e.HISTORY_NO order by e.HISTORY_NO DESC), ',', 1) AS HISTORY_NO

FROM ORDER_SHEET E

JOIN LAB_HISTORY G ON E.HISTORY_NO = G.HISTORY_NO

JOIN PROJECT_INFO K ON G.PROJECT_NO = K.PROJECT_NO

WHERE E.USE_YN = 'Y' AND G.USE_YN='Y' AND K.USE_YN = 'Y'

GROUP BY K.PROJECT_ID) M

ON E.HISTORY_NO = M.HISTORY_NO;

HISTORY_NO 컬럼값 외에 다른 컬럼값을 가져오기 위해서는 Self Join을 한번 해야한다.

그런데 위처럼 ORDER BY 해서 가장 큰 값만 필요하다면 그냥 MAX() 를 써도 된다.

-- PROJECT_ID 별로 HISTORY_NO 값이 가장 큰 레코드를 추출하는 쿼리.

SELECT M.PROJECT_ID, E.* 

FROM ORDER_SHEET E JOIN (

SELECT

K.PROJECT_ID, 

MAX(e.HISTORY_NO) AS HISTORY_NO

FROM ORDER_SHEET E

JOIN LAB_HISTORY G ON E.HISTORY_NO = G.HISTORY_NO

JOIN PROJECT_INFO K ON G.PROJECT_NO = K.PROJECT_NO

WHERE E.USE_YN = 'Y' AND G.USE_YN='Y' AND K.USE_YN = 'Y'

GROUP BY K.PROJECT_ID) M

ON E.HISTORY_NO = M.HISTORY_NO;

그냥 MAX() 쓰면 되는데, 이제까지 뭐 한 거지... 쩝.

[참고]

Using LIMIT within GROUP BY to get N results per group?

http://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group

SQL: selecting top N records per group

http://code.openark.org/blog/mysql/sql-selecting-top-n-records-per-group

Rank function in MySQL

http://stackoverflow.com/questions/3333665/rank-function-in-mysql

위 웹 페이지에 보면 변수를 사용하지 않고 Self Join 만을 사용해서 Rank를 구하는 쿼리가 나와 있다.

While the most upvoted answer ranks, it doesn't partition, You can do a self Join to get the whole thing partitioned also:

SELECT    a.first_name,
      a.age,
      a.gender,
        count(b.age)+1 as rank
FROM  person a left join person b on a.age>b.age and a.gender=b.gender 
group by  a.first_name,
      a.age,
      a.gender

Use Case

CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1));

INSERT INTO person VALUES (1, 'Bob', 25, 'M');
INSERT INTO person VALUES (2, 'Jane', 20, 'F');
INSERT INTO person VALUES (3, 'Jack', 30, 'M');
INSERT INTO person VALUES (4, 'Bill', 32, 'M');
INSERT INTO person VALUES (5, 'Nick', 22, 'M');
INSERT INTO person VALUES (6, 'Kathy', 18, 'F');
INSERT INTO person VALUES (7, 'Steve', 36, 'M');
INSERT INTO person VALUES (8, 'Anne', 25, 'F');

Answer:

Bill    32  M   4
Bob     25  M   2
Jack    30  M   3
Nick    22  M   1
Steve   36  M   5
Anne    25  F   3
Jane    20  F   2
Kathy   18  F   1