Return row with latest timestamp from groupings of revisions

DonN · February 16, 2021, 1:32am

Dataset
drop table #sampledata
create table #sampledata
(
ResourceID int,
RevisionID int,
TimeStamp datetime,
data1 varchar(50),
data2 varchar(50),
)
go

insert into #sampledata values
(1,1,'2021-02-09 22:20:25','15_1','14_1'),
(1,2,'2021-02-10 12:15:20','14_1,15_1,15_2','14_1'),
(1,3,'2021-02-10 22:40:40','14_1,15_1,15_2,15_3','14_1,15_1'),
(2,1,'2021-02-10 06:25:29','14_1,15_1','13_1,14_1'),
(3,1,'2021-02-10 03:44:38','15_1','13_1,14_1'),
(4,1,'2021-02-10 12:25:19','14_1','13_1,14_1'),
(5,1,'2021-02-10 11:36:35','13_1',null),
(6,1,'2021-02-10 03:44:38', null,'14_1')
select * from #sampledata

I'm using the following query below to create the result set, however, it's returning results based on the data contained in the three ResourceID 1 rows in the dataset whereas I need it to return results based on the most recent ResourceID 1 row using TimeStamp. I was thinking I could use Top 1 here, however, I'm not quite sure how to insert it into this query.

SELECT ca1.*
FROM #sampledata sd

CROSS APPLY (
SELECT SD.ResourceID, SD.TimeStamp, '13_Data1' AS 'Product Name', 'null' AS 'Product Version', 'xxx' AS 'Publisher', 'null' AS 'Install Date'
WHERE SD.data1 LIKE '%13%'
UNION ALL
SELECT SD.ResourceID, SD.TimeStamp, '14_Data1' AS 'Product Name', 'null' AS 'Product Version', 'xxx' AS 'Publisher', 'null' AS 'Install Date'
WHERE SD.data1 LIKE '%14%'
UNION ALL
SELECT SD.ResourceID, SD.TimeStamp, '15_Data1' AS 'Product Name', 'null' AS 'Product Version', 'xxx' AS 'Publisher', 'null' AS 'Install Date'
WHERE SD.data1 LIKE '%15%'
UNION ALL
SELECT SD.ResourceID, SD.TimeStamp, '13_Data2' AS 'Product Name', 'null' AS 'Product Version', 'xxx' AS 'Publisher', 'null' AS 'Install Date'
WHERE SD.data2 LIKE '%13%'
UNION ALL
SELECT SD.ResourceID, SD.TimeStamp, '14_Data2' AS 'Product Name', 'null' AS 'Product Version', 'xxx' AS 'Publisher', 'null' AS 'Install Date'
WHERE SD.data2 LIKE '%14%'
UNION ALL
SELECT SD.ResourceID, SD.TimeStamp, '15_Data2' AS 'Product Name', 'null' AS 'Product Version', 'xxx' AS 'Publisher', 'null' AS 'Install Date'
WHERE SD.data2 LIKE '%15%'
) AS ca1

Desired results - Displaying rows with only the latest timestamp for ResourceID 1

harishgg1 · February 16, 2021, 7:43am

hi hope this helps

; with cte as 
(
	select 
	   a.* 
    from 
	   #sampledata a 
	      where a.TimeStamp in 
		        (select maxts from (select ResourceID, max(TimeStamp) as maxts from #sampledata sd group by ResourceID ) b)
)
SELECT ca1.*
FROM cte sd
CROSS APPLY (
SELECT SD.ResourceID, SD.TimeStamp, '13_Data1' AS 'Product Name', 'null' AS 'Product Version', 'xxx' AS 'Publisher', 'null' AS 'Install Date'
WHERE SD.data1 LIKE '%13%'
UNION ALL
SELECT SD.ResourceID, SD.TimeStamp, '14_Data1' AS 'Product Name', 'null' AS 'Product Version', 'xxx' AS 'Publisher', 'null' AS 'Install Date'
WHERE SD.data1 LIKE '%14%'
UNION ALL
SELECT SD.ResourceID, SD.TimeStamp, '15_Data1' AS 'Product Name', 'null' AS 'Product Version', 'xxx' AS 'Publisher', 'null' AS 'Install Date'
WHERE SD.data1 LIKE '%15%'
UNION ALL
SELECT SD.ResourceID, SD.TimeStamp, '13_Data2' AS 'Product Name', 'null' AS 'Product Version', 'xxx' AS 'Publisher', 'null' AS 'Install Date'
WHERE SD.data2 LIKE '%13%'
UNION ALL
SELECT SD.ResourceID, SD.TimeStamp, '14_Data2' AS 'Product Name', 'null' AS 'Product Version', 'xxx' AS 'Publisher', 'null' AS 'Install Date'
WHERE SD.data2 LIKE '%14%'
UNION ALL
SELECT SD.ResourceID, SD.TimeStamp, '15_Data2' AS 'Product Name', 'null' AS 'Product Version', 'xxx' AS 'Publisher', 'null' AS 'Install Date'
WHERE SD.data2 LIKE '%15%'
) AS ca1

DonN · February 17, 2021, 2:36am

@harishgg1 That was helpful, however, I ran into an issue with the following data set. In essence, resourceid 16777323 is being returned with different timestamps instead of the most recent.

insert into #sampledata values
(16777323,1,'2021-02-10 11:36:35.000','14_1,15_1,15_2',null),
(16777323,2,'2021-02-10 15:47:12.000','14_1,15_1,15_2',null),
(16777323,3,'2021-02-11 08:20:23.000',null,null),
(16777323,4,'2021-02-11 15:45:59.000','14_1,15_1,15_2',null),
(2,1,'2021-02-10 06:25:29','14_1,15_1','13_1,14_1'),
(3,1,'2021-02-10 03:44:38','15_1','13_1,14_1'),
(4,1,'2021-02-10 12:25:19','14_1','13_1,14_1'),
(5,1,'2021-02-10 11:36:35','13_1',null),
(6,1,'2021-02-10 03:44:38', null,'14_1')

harishgg1 · February 17, 2021, 6:27am

hope this helps ..

the reason is "the same time stamp exists for 2 different resource ids"

i changed my SQL to include resource id when getting the max time stamp

; with cte as 
(
	select 
	   a.* 
    from 
	   #sampledata a 
	      where EXISTS  
(SELECT *   
    FROM  (select ResourceID, max(TimeStamp) as maxts from #sampledata sd group by ResourceID ) b 
    WHERE a.ResourceID = b.ResourceID  AND a.TimeStamp = b.maxts)   		        
)
SELECT ca1.*
FROM cte sd
CROSS APPLY (
SELECT SD.ResourceID, SD.TimeStamp, '13_Data1' AS 'Product Name', 'null' AS 'Product Version', 'xxx' AS 'Publisher', 'null' AS 'Install Date'
WHERE SD.data1 LIKE '%13%'
UNION ALL
SELECT SD.ResourceID, SD.TimeStamp, '14_Data1' AS 'Product Name', 'null' AS 'Product Version', 'xxx' AS 'Publisher', 'null' AS 'Install Date'
WHERE SD.data1 LIKE '%14%'
UNION ALL
SELECT SD.ResourceID, SD.TimeStamp, '15_Data1' AS 'Product Name', 'null' AS 'Product Version', 'xxx' AS 'Publisher', 'null' AS 'Install Date'
WHERE SD.data1 LIKE '%15%'
UNION ALL
SELECT SD.ResourceID, SD.TimeStamp, '13_Data2' AS 'Product Name', 'null' AS 'Product Version', 'xxx' AS 'Publisher', 'null' AS 'Install Date'
WHERE SD.data2 LIKE '%13%'
UNION ALL
SELECT SD.ResourceID, SD.TimeStamp, '14_Data2' AS 'Product Name', 'null' AS 'Product Version', 'xxx' AS 'Publisher', 'null' AS 'Install Date'
WHERE SD.data2 LIKE '%14%'
UNION ALL
SELECT SD.ResourceID, SD.TimeStamp, '15_Data2' AS 'Product Name', 'null' AS 'Product Version', 'xxx' AS 'Publisher', 'null' AS 'Install Date'
WHERE SD.data2 LIKE '%15%'
) AS ca1

harishgg1 · February 17, 2021, 9:04am

DonN · February 17, 2021, 12:18pm

This was very helpful. Thank you for helping me with this.