1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | -- MySQL 8.0 이상 분석함수 지원 버전인 경우 -- SELECT idx, common_idx, name FROM ( SELECT idx, common_idx, name , ROW_NUMBER() OVER(PARTITION BY common_idx ORDER BY idx DESC ) rn FROM t1 ) a WHERE rn = 1 ; -- MySQL 8.0 미만 -- SELECT a.* FROM t1 a INNER JOIN ( SELECT MAX (idx) idx FROM t1 GROUP BY common_idx) b ON a.idx = b.idx ; SELECT a.* FROM t1 a LEFT OUTER JOIN t1 b ON a.common_idx = b.common_idx AND a.idx < b.idx WHERE b.idx IS NULL ; SELECT * FROM t1 a WHERE NOT EXISTS ( SELECT 1 FROM t1 WHERE common_idx = a.common_idx AND idx > a.idx) ; SELECT * FROM t1 a WHERE idx = ( SELECT MAX (idx) FROM t1 WHERE common_idx = a.common_idx) ; SELECT * FROM t1 WHERE idx IN ( SELECT MAX (idx) FROM t1 GROUP BY common_idx) ; SELECT MAX (idx) idx , common_idx , SUBSTR( MAX (CONCAT(LPAD(idx, 10, 0), name )), 11, 30) name FROM t1 GROUP BY common_idx ; |