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?
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.
Table 'TwoIds'. Scan count 3, logical reads 6, 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 = 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.