เทคนิคการ Query ข้อมูลล่าสุดในแต่ละกลุ่ม (ตอนที่ 2)

drop table if exists table_name;
create table table_name (id int not null primary key auto_increment, status int, repair_id int);
drop procedure if exists spGenData;
delimiter //
create procedure spGenData(maxRows int)
begin
declare i int;
set i = 1;
while (i < maxRows) do
insert table_name values(i, 1 + FLOOR(RAND()*100), 1 + FLOOR(RAND()*100));
set i = i + 1;
end while;
end;
//
delimiter ;
call spGenData(10000);
drop procedure spGenData;

SELECT `id`,`status`,`repair_id`
FROM `table_name` AS S
WHERE S.`id`=(
  SELECT MAX(`id`)
  FROM `table_name`
  WHERE `repair_id`=S.`repair_id`
)

SELECT S.`id`,S.`status`,S.`repair_id`
FROM `table_name` AS S
JOIN (
  SELECT `repair_id`, max(`id`) AS `max_id`
  FROM `table_name`
  GROUP BY `repair_id`
) AS T ON T.`repair_id`=S.`repair_id` AND S.`id`=T.`max_id`

CREATE INDEX `repair_id` ON `table_name` (`repair_id`);