Another variation would be
-- create table code
CREATE TABLE dbo.MassiveTable
([Tid] int, [Bid] varchar(1), [Action] varchar(6), [Status] varchar(8), [Value] int, [Time] time)
;
INSERT INTO dbo.MassiveTable
([Tid], [Bid], [Action], [Status], [Value], [Time])
VALUES
(1, 'T', 'Insert', NULL, 50, '10:11'),
(1, 'T', 'Update', 'Executed', 50, '10:12'),
(1, 'T', 'Remove', 'Executed', 50, '10:50'),
(1, 'S', 'Insert', NULL, 10, '10:10'),
(1, 'S', 'Update', 'Executed', 10, '10:11'),
(2, 'T', 'Insert', 'Executed', 22, '12:20'),
(2, 'T', 'Remove', 'Executed', 22, '12:44'),
(3, 'B', 'Insert', 'Executed', 44, '15:21'),
(3, 'B', 'Update', 'Executed', 48, '15:25')
;
--- query
set nocount on
set statistics time, io on
select [Tid], [Bid], [Action], [Status], [Value], left([Time],5) as Time
from
(
select [Tid], [Bid], [Action], [Status], [Value], [Time],
rank() over(partition by [Tid], [Bid]
order by [Time] desc) seq
from dbo.MassiveTable
where [Status]='executed'
) d
where seq = 1 and [Action] <> 'Remove'
set statistics time, io off
The statistics time and IO are as below :
Table 'MassiveTable'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 32 ms.
Execution plan would look like
