SQL 分群 找最新一筆

//[http://pclevinblog.pixnet.net/blog/post/314563327-%5Bmysql%5D2-10%E4%BD%BF%E7%94%A8max%E5%8F%8Amin%E5%8F%8Agroup-by]
SELECT MAX(p.price),p.order_id,p.product_name FROM products_table AS p GROUP BY p.order_id; 

//[http://shelleyura.pixnet.net/blog/post/21075144-sql-query%3A-%E6%9F%A5%E8%A9%A2%E6%AF%8F%E5%80%8Bid%2C-%E6%9C%80%E6%96%B0%E4%B8%80%E7%AD%86%E7%9A%84%E8%A8%98%E9%8C%84]
select id, max(date) from tablename group by id; 

//[http://wangshifuola.blogspot.com/2010/07/sql.html]
select PatrolSaveShip.SaveShipID,PatrolSaveShip.State,PatrolSaveShip.PatrolName,PatrolSaveShip.PatrolDate,PatrolSaveShip.Mark
from PatrolSaveShip,(select SaveShipID, max(PatrolDate) as maxtime from PatrolSaveShip group by SaveShipID) as idview
WHERE (PatrolSaveShip.SaveShipID = idview.SaveShipID) and (PatrolSaveShip.PatrolDate = idview.maxtime);

//[https://dev-felix72.blogspot.com/2016/10/sql-group-by-then-get-max-or-min-item-in-each-group.html]
SELECT t.Train, t.Dest, r.MaxTime FROM ( SELECT Train, MAX(Time) as MaxTime FROM TrainTable GROUP BY Train ) r INNER JOIN TrainTable t ON t.Train = r.Train AND t.Time = r.MaxTime;

//[https://www.cnblogs.com/Alight/p/3425357.html]
SELECT * FROM (SELECT * FROM posts ORDER BY dateline DESC) GROUP BY  tid ORDER BY dateline DESC LIMIT 10;

//[http://felixhuang.pixnet.net/blog/post/20414464-%5Bmssql%5D-%E6%9F%A5%E8%A9%A2%E9%87%8D%E8%A6%86%E8%B3%87%E6%96%99%E4%B8%AD%E6%97%A5%E6%9C%9F%E6%9C%80%E5%A4%A7%E8%80%85]
Select * From Order a Where Date = (Select Max(b.Date) From Order b Where a.ID = b.ID);

//目前使用01、02的寫法
//實際語法如下，建立base資料表

SELECT base.timestamp AS timestamp,base.status AS status,base.door_index AS door_index,base.serial_number AS serial_number,base.card_unique_identifier AS card_unique_identifier,rs.name AS n_status,d.name AS dname FROM 
(SELECT Max(cir.timestamp) AS timestamp,cir.status AS status,cir.door_index AS door_index,CONV(cir.serial_number,10,16) AS serial_number,cir.card_unique_identifier AS card_unique_identifier FROM controller_io_record AS cir WHERE cir.status IN ('1','2','3','4','5','6','7','8','17','18','19','20','21','22','23','24','25','26') GROUP BY cir.card_unique_identifier LIMIT 0,1000) AS base,
record_status AS rs,door AS d 
WHERE (base.status=rs.id) AND (d.controller_id=base.serial_number) AND (d.controller_door_index=base.door_index);