Skip to main content
Database Administrators

Return to Answer

added 760 characters in body
Source Link
Aaron Bertrand
Aaron Bertrand
  • 182.2k
  • 28
  • 407
  • 626

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.tablename WHERE [Status] = 'Executed'MassiveTable ) SELECT Tid, Bid, [Action], [Status], Value, [Time] FROM x WHERE Status = 'Executed' AND rn = 1 AND NOT EXISTS ( SELECT 1 FROM dbo.tablenamex 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  (  -- ANDSELECT t.Tid, Bid, [Action], [Status], Value, [Time], >rn x= 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 unclearWHERE if[Action] earlier<> removes'Remove' shouldAND bern ignored= 1;

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

(3, 'B', 'Remove', 'Executed', 45, '15:23');,

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 ORDER BY [time] DESC) FROM dbo.tablename WHERE [Status] = 'Executed' ) SELECT Tid, Bid, [Action], [Status], Value, [Time] FROM x WHERE rn = 1 AND NOT EXISTS ( SELECT 1 FROM dbo.tablename AS t WHERE t.Tid = x.Tid AND t.Bid = x.Bid AND t.[Action] = 'Remove' -- AND t.[Time] > x.[Time] -- unclear if earlier removes should be ignored );

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'),
edited body
Source Link
Aaron Bertrand
Aaron Bertrand
  • 182.2k
  • 28
  • 407
  • 626

I think it can be expressed a little more succinctly:

;WITH x AS ( SELECT Tid, Bid, Action[Action], [Status], Value, [Time], rn = ROW_NUMBER() OVER (PARTITION BY Tid, Bid ORDER BY [time] DESC) FROM dbo.tablename WHERE [Status] = 'Executed' ) SELECT Tid, Bid, Action[Action], [Status], Value, [Time] FROM x WHERE rn = 1 AND NOT EXISTS ( SELECT 1 FROM dbo.tablename AS t WHERE t.Tid = x.Tid AND t.Bid = x.Bid AND t.[Status][Action] = 'Remove' -- AND t.[Time] > x.[Time] -- unclear if earlier removes should be ignored );

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 ORDER BY [time] DESC) FROM dbo.tablename WHERE [Status] = 'Executed' ) SELECT Tid, Bid, Action, [Status], Value, [Time] FROM x WHERE rn = 1 AND NOT EXISTS ( SELECT 1 FROM dbo.tablename AS t WHERE t.Tid = x.Tid AND t.Bid = x.Bid AND t.[Status] = 'Remove' -- AND t.[Time] > x.[Time] -- unclear if earlier removes should be ignored );

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 ORDER BY [time] DESC) FROM dbo.tablename WHERE [Status] = 'Executed' ) SELECT Tid, Bid, [Action], [Status], Value, [Time] FROM x WHERE rn = 1 AND NOT EXISTS ( SELECT 1 FROM dbo.tablename AS t WHERE t.Tid = x.Tid AND t.Bid = x.Bid AND t.[Action] = 'Remove' -- AND t.[Time] > x.[Time] -- unclear if earlier removes should be ignored );
Source Link
Aaron Bertrand
Aaron Bertrand
  • 182.2k
  • 28
  • 407
  • 626

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 ORDER BY [time] DESC) FROM dbo.tablename WHERE [Status] = 'Executed' ) SELECT Tid, Bid, Action, [Status], Value, [Time] FROM x WHERE rn = 1 AND NOT EXISTS ( SELECT 1 FROM dbo.tablename AS t WHERE t.Tid = x.Tid AND t.Bid = x.Bid AND t.[Status] = 'Remove' -- AND t.[Time] > x.[Time] -- unclear if earlier removes should be ignored );