# Exclude rows from SELECT

I have a massive table where a row is defined by two ids (Tid,Bid) that looks like this:

------------------------------------------------------
| Tid | Bid |  Action  | Status     | Value |  Time  |
|  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 |
------------------------------------------------------

From this table I want to select only rows with the latest time and have the status Executed and exclude all rows with the same ids if one of those rows contained the Action Remove, so the final resoult should look like this :

------------------------------------------------------
| Tid | Bid |  Action  | Status     | Value |  Time  |
|  1  |  S  |   Update |   Executed |   10  |  10:11 |
|  3  |  B  |   Update |   Executed |   48  |  15:25 |
------------------------------------------------------

Currently my solution is built with multiple nested Selects and it dosent run very fast, is there a more elegant way to solve this?

I think it can be expressed a little more succinctly:

;WITH x AS
(
SELECT Tid, Bid, [Action], [Status], Value, [Time], rn = ROW_NUMBER()
OVER (PARTITION BY Tid, Bid, [Status] ORDER BY [time] DESC)
FROM dbo.MassiveTable
)
SELECT Tid, Bid, [Action], [Status], Value, [Time]
FROM x WHERE Status = 'Executed' AND rn = 1
AND NOT EXISTS
(
SELECT 1 FROM x AS t
WHERE t.Tid = x.Tid
AND t.Bid = x.Bid
AND t.[Action] = 'Remove'
);

Note that this assumes that a Remove value anywhere in the timeline makes that Tid, Bid combination invalid. If a Remove is always guaranteed to be last, and the only one in the timeline for that combination (or you don't care about earlier removes as long as they weren't the last entry), you can cheat and get a slightly more efficient plan:

;WITH x AS
(
SELECT Tid, Bid, [Action], [Status], Value, [Time], rn = ROW_NUMBER()
OVER (PARTITION BY Tid, Bid, [Status] ORDER BY [time] DESC)
FROM dbo.MassiveTable
WHERE [Status] = 'Executed'
)
SELECT Tid, Bid, [Action], [Status], Value, [Time]
FROM x WHERE [Action] <> 'Remove' AND rn = 1;

But those assumptions are important. Test all queries with this additional row of sample data:

(3, 'B', 'Remove', 'Executed', 45, '15:23'),
• Currently this query is slower by about ~ 30ms-50ms but it returns more rows then the query written by @Kin , I will have to do some more analyzing which is more correct. Both complete at about 1450ms as compared to mine abomination which ran for more than 5min. Commented Nov 25, 2015 at 16:13
• @Hauba Sorry, I mixed up Status and Action. Corrected now. Commented Nov 25, 2015 at 17:01
• @Hauba Also, there is a semantic difference in our queries. Kin's assumes that the Remove row will always be the last entry for a Tid/Bid combo. My first query will exclude any combos where a remove exists, even if it's not the last entry. My second query should behave more like Kin's. Commented Nov 25, 2015 at 17:06
• The queries work perfectly. The semantic diffrence just became very important as I got a case where a tid/bid row got updated after it was flaged as removed, so I need confirmation what that means, but your anwser cover it all. Commented Nov 27, 2015 at 10:03

What are you using to measure the speed of your query? Without a baseline, how will we know if the way that we do it will be faster?

It could also be useful to add in maybe a snippet of your query, even if it's just to check that we are not doing the same that you are.

Creating and populating a test table with the information that you have given us, on SQL Server 2012:

if object_id('dbo.TwoIds', 'U') is not null
begin
drop table dbo.TwoIds;
end;

create table TwoIds
(
Tid int
, Bid char(1)
, [Action] varchar(25)
, [Status] varchar(25)
, [Value] int
, [Time] time
);

insert TwoIds
(Tid, Bid, [Action], [Status], [Value], [Time])
select
*
from
(
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')
) as data(Tid, Bid, [Action], [Status], Value, [Time]);

That should give us your table named TwoIds that we can test against.

The following query is what I would have used to get the results that you want.

with CTE_LatestWithExecuted as
(
select
*
, [Ordering] = ROW_NUMBER() over (partition by Tid, Bid order by [Time] asc)
from TwoIds
),
CTE_Order as
(
select
LastId = max(Ordering)
, Tid
, Bid
from CTE_LatestWithExecuted as le
where [Status] = 'Executed'
group by
Tid
, Bid
)
select
cle.*
from CTE_Order as ceo
inner join CTE_LatestWithExecuted as cle on ceo.Tid = cle.Tid and ceo.Bid = cle.Bid and ceo.LastId = cle.Ordering
where not exists
(
select 1
from CTE_LatestWithExecuted as ne
where ne.Tid = cle.Tid
and ne.Bid = cle.bid
and ne.[Action] = 'Remove'
);

From checking the statistics and time on the query, I'm getting the following results:

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 13 ms.

SQL Server Execution Times:
CPU time = 15 ms,  elapsed time = 0 ms.

It's just one possible way that could be done but hopefully it will give you another option on aa way to do this. With testing you could see if that is performs better for you.

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 :