SQL: Get last row for each unique id

Question

I have a table. I want to get the last row for each unique CoatingChambersID. There are 24 unique chambers, and the last row indicates how long the chamber has been in the last state.

I can get a unique list like so:

SELECT distinct CoatingChambersID, MAX(LastDT) as LastDT
  FROM [REO].[dbo].[CoatingChamberStateLogs] 
  group by CoatingChambersID

But I cannot figure out how to join that back to the original table.

Thanks in advance for your help!

CoatingChamberStateLogsID   CoatingChambersID   StartDT LastDT  IntervalMin  CoatingChamberStatesID 
1001712 1003    2017-01-24 23:09:29.4632108 2017-01-25 03:04:32.6758500 235.053543986667    1007    
1001713 1006    2017-01-24 23:09:29.4632108 2017-01-25 00:30:30.8478433 81.0230772083333    1007    
1001714 1015    2017-01-24 23:09:29.4632108 2017-01-25 04:21:33.7601011 312.071614838333    1007    
1001715 1024    2017-01-24 23:09:29.4632108 2017-01-31 04:43:21.5643016 8973.86835151333    1001    
1001716 1016    2017-01-24 23:09:29.4632108 2017-01-25 00:01:30.4200122 52.01594669 1006    
1001717 1017    2017-01-24 23:09:29.4632108 2017-01-24 23:15:29.8261612 6.00604917333333    1006    
1001718 1018    2017-01-24 23:09:29.4632108 2017-01-26 01:42:49.4040548 1593.3323474    1006    0   
1001719 1019    2017-01-24 23:09:29.4632108 2017-01-25 02:25:32.3047026 196.047358196667    1005    
1001720 1020    2017-01-24 23:09:29.4632108 2017-01-24 23:12:29.8009482 3.00562895666667    1007    
1001721 1022    2017-01-24 23:09:29.4632108 2017-01-25 02:52:32.5995144 223.052271726667    1007    
1001722 1023    2017-01-24 23:09:29.4632108 2017-01-25 03:05:32.9236594 236.057674143333    1007    
1001723 1002    2017-01-24 23:09:29.4475820 2017-01-25 02:14:32.1723891 185.045413451667    1001    
1001724 1004    2017-01-24 23:09:29.4632108 2017-01-25 00:39:31.0878194 90.02707681 1001    
1001725 1005    2017-01-24 23:09:29.4632108 2017-01-24 23:18:29.8783565 9.006919095 1001

I added the SQL Server tag because the SQL looks like SQL Server. — Gordon Linoff, Jul 24 '17 at 15:06
Possible duplicate of [Retrieving the last record in each group](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) — Tab Alleman, Jul 24 '17 at 15:15

score 7 · Accepted Answer · answered Jul 24 '17 at 15:06

7

Use row_number():

select ccs.*
from (select ccs.*,
             row_number() over (partition by ccs.CoatingChambersID order by ccs.LastDt desc) as seqnum
      from [REO].[dbo].[CoatingChamberStateLogs] ccs
     ) ccs
where seqnum = 1;

The use of select distinct with group by is almost never correct.

Another fun way to write the query doesn't require a subquery:

select top (1) with ties ccs.*
from [REO].[dbo].[CoatingChamberStateLogs] ccs
order by row_number() over (partition by ccs.CoatingChambersID order by ccs.LastDt desc);

answered Jul 24 '17 at 15:06

Gordon Linoff

1,242,037
58
646
786

Damn you're fast! – Zohar Peled Jul 24 '17 at 15:08
Would cross apply self join be faster? I like the `with ties`.. order by trick to avoid the subquery. – xQbert Jul 24 '17 at 15:19
1

@xQbert - Cross apply will not be faster in this case.. It will be faster when you have distinct list of `CoatingChambersID` stored in some other table then you want top 1 record for each `CoatingChambersID` in `CoatingChamberStateLogs` table.. Here clearly `Row_Number` is the best approach.. though `top 1 with ties` will be slow.. – Pரதீப் Jul 24 '17 at 15:28

score 0 · Answer 2 · answered Jul 24 '17 at 15:26

While I like Gordon's answer... I wanted to provide a secondary alternative. I've not taken the time to determine if a cross apply would be more efficient; but given the cost of the join here I would think his maybe more efficient. but without testing I can't say.

SELECT B.*
FROM [REO].[dbo].[CoatingChamberStateLogs] A
CROSS APPLY (SELECT TOP 1 * 
             FROM [REO].[dbo].[CoatingChamberStateLogs]  B
             WHERE A.CoatingChambersID = B.CoatingChambersID
             ORDER BY lastDT Desc)

I know it works well in many of the top n records in 1-M relationships but as this is a self join his response may result in a better execution plan as it avoids the join.

SQL: Get last row for each unique id

2 Answers2

Linked