積沙成塔

今天在論壇上看到有人在問說使用Group By來找出同一個ID且最晚時間的資料，筆者使用了RANK函式搭配CTE來產生發問者所要的結果，說明如下：

• 以下程式碼範例為建立測試資料之用：

   1:  declare @t table

   2:  (

   3:      [ID] int

   4:      ,[EVENT] varchar(10)

   5:      ,[Time] time

   6:  )

   7:  

   8:  insert into @t values(1,'live','09:00'),(2,'live','09:00'),(3,'live','09:00'),(1,'live','09:10')

   9:  ,(2,'live','09:10'),(3,'live','09:10'),(3,'dead','09:15'),(2,'dead','09:25')

• 以下程式碼範例為使用RANK函式來針對ID和TIME進行排序，在依ID進行排名次，便可得到下圖的結果，由此可看出同一個ID，最晚時間的紀錄其SN一定最大（螢光筆所標示處）。

   1:  ;with cte_t

   2:  as 

   3:  (

   4:  select RANK() OVER (PARTITION BY [ID] ORDER BY [ID],[TIME]) AS SN

   5:  ,[ID],[EVENT],[Time]

   6:  from @t t1

   7:  )

• 最後將以ID分群取得最大SN的結果與CTE進行join，以得到如下圖的答案。

   1:  select t1.[ID],t1.[EVENT],t1.[Time]

   2:  from cte_t t1

   3:  inner join 

   4:  (select [ID],MAX(SN) AS SN

   5:  from cte_t

   6:  group by [ID]) t2

   7:  on t1.ID = t2.ID and t1.SN = t2.SN

 

【參考資料】

• http://msdn.microsoft.com/zh-tw/library/ms176102.aspx
• http://msdn.microsoft.com/zh-tw/library/ms175972.aspx