SQL 分群 找最新一筆 – jashliao部落格

SQL 分群 找最新一筆

//[http://pclevinblog.pixnet.net/blog/post/314563327-[mysql]2-10使用max及min及group-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:-查詢每個id,-最新一筆的記錄]
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-[mssql]-查詢重覆資料中日期最大者]
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);