clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1044238 fiddles created (9452 in the last week).

CREATE TABLE #Example ( UniqueID INT IDENTITY(1,1) , GroupID INT , GroupDate DATETIME , UniqueDate DATETIME ) SET NOCOUNT ON --Populate some test data DECLARE @i INT = 0, @j INT = 5, @UniqueDate DATETIME, @GroupDate DATETIME WHILE @i < 10000 BEGIN IF((@i + @j)%173 = 0) BEGIN SET @UniqueDate = GETDATE()+@i+5 END ELSE BEGIN SET @UniqueDate = GETDATE()+@i END SET @GroupDate = GETDATE()+(@j-1) INSERT INTO #Example (GroupID, GroupDate, UniqueDate) VALUES (@j, @GroupDate, @UniqueDate) SET @i = @i + 1 IF (@i % 5 = 0) BEGIN SET @j = @j+5 END END SET NOCOUNT OFF
 hidden batch(es)


set statistics time, io, xml on; -- Identify any UniqueDates that are greater than the GroupDate within their GroupID SELECT UniqueID , GroupID , GroupDate , UniqueDate FROM ( SELECT UniqueID , GroupID , GroupDate , UniqueDate , MAX(UniqueDate) OVER (PARTITION BY GroupID) AS maxUniqueDate FROM #Example ) calc_maxUD WHERE maxUniqueDate > GroupDate AND maxUniqueDate = UniqueDate
UniqueID GroupID GroupDate UniqueDate
172 175 05/10/2017 20:43:09 07/10/2017 20:43:09
260 260 29/12/2017 20:43:09 03/01/2018 20:43:09
431 435 22/06/2018 20:43:09 23/06/2018 20:43:09
519 520 15/09/2018 20:43:09 19/09/2018 20:43:09
778 780 02/06/2019 20:43:09 05/06/2019 20:43:09
1037 1040 17/02/2020 20:43:09 19/02/2020 20:43:09
1125 1125 12/05/2020 20:43:09 17/05/2020 20:43:09
1296 1300 03/11/2020 20:43:09 04/11/2020 20:43:09
1384 1385 27/01/2021 20:43:09 31/01/2021 20:43:09
1643 1645 14/10/2021 20:43:09 17/10/2021 20:43:09
1902 1905 01/07/2022 20:43:09 03/07/2022 20:43:09
1990 1990 24/09/2022 20:43:09 29/09/2022 20:43:09
2161 2165 18/03/2023 20:43:09 19/03/2023 20:43:09
2249 2250 11/06/2023 20:43:09 15/06/2023 20:43:09
2508 2510 26/02/2024 20:43:09 29/02/2024 20:43:09
2767 2770 12/11/2024 20:43:09 14/11/2024 20:43:09
2855 2855 05/02/2025 20:43:09 10/02/2025 20:43:09
3026 3030 30/07/2025 20:43:09 31/07/2025 20:43:09
3114 3115 23/10/2025 20:43:09 27/10/2025 20:43:09
3373 3375 10/07/2026 20:43:09 13/07/2026 20:43:09
3632 3635 27/03/2027 20:43:09 29/03/2027 20:43:09
3720 3720 20/06/2027 20:43:09 25/06/2027 20:43:09
3891 3895 12/12/2027 20:43:09 13/12/2027 20:43:09
3979 3980 06/03/2028 20:43:09 10/03/2028 20:43:09
4238 4240 21/11/2028 20:43:09 24/11/2028 20:43:09
4497 4500 08/08/2029 20:43:09 10/08/2029 20:43:09
4585 4585 01/11/2029 20:43:09 06/11/2029 20:43:09
4756 4760 25/04/2030 20:43:09 26/04/2030 20:43:09
4844 4845 19/07/2030 20:43:09 23/07/2030 20:43:09
5103 5105 05/04/2031 20:43:09 08/04/2031 20:43:09
5362 5365 21/12/2031 20:43:09 23/12/2031 20:43:09
5450 5450 15/03/2032 20:43:09 20/03/2032 20:43:09
5621 5625 06/09/2032 20:43:09 07/09/2032 20:43:09
5709 5710 30/11/2032 20:43:09 04/12/2032 20:43:09
5968 5970 17/08/2033 20:43:09 20/08/2033 20:43:09
6227 6230 04/05/2034 20:43:09 06/05/2034 20:43:09
6315 6315 28/07/2034 20:43:09 02/08/2034 20:43:09
6486 6490 19/01/2035 20:43:09 20/01/2035 20:43:09
6574 6575 14/04/2035 20:43:09 18/04/2035 20:43:09
6833 6835 30/12/2035 20:43:09 02/01/2036 20:43:09
7092 7095 15/09/2036 20:43:09 17/09/2036 20:43:09
7180 7180 09/12/2036 20:43:09 14/12/2036 20:43:09
7351 7355 02/06/2037 20:43:09 03/06/2037 20:43:09
7439 7440 26/08/2037 20:43:09 30/08/2037 20:43:09
7698 7700 13/05/2038 20:43:09 16/05/2038 20:43:09
7957 7960 28/01/2039 20:43:09 30/01/2039 20:43:09
8045 8045 23/04/2039 20:43:09 28/04/2039 20:43:09
8216 8220 15/10/2039 20:43:09 16/10/2039 20:43:09
8304 8305 08/01/2040 20:43:09 12/01/2040 20:43:09
8563 8565 24/09/2040 20:43:09 27/09/2040 20:43:09
8822 8825 11/06/2041 20:43:09 13/06/2041 20:43:09
8910 8910 04/09/2041 20:43:09 09/09/2041 20:43:09
9081 9085 26/02/2042 20:43:09 27/02/2042 20:43:09
9169 9170 22/05/2042 20:43:09 26/05/2042 20:43:09
9428 9430 06/02/2043 20:43:09 09/02/2043 20:43:09
9687 9690 24/10/2043 20:43:09 26/10/2043 20:43:09
9775 9775 17/01/2044 20:43:09 22/01/2044 20:43:09
9946 9950 10/07/2044 20:43:09 11/07/2044 20:43:09
Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.5" Build="13.0.4001.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT UniqueID&#xa;&#x9; , GroupID&#xa;&#x9; , GroupDate&#xa;&#x9; , UniqueDate&#xa;FROM (&#xa;&#x9;SELECT UniqueID&#xa;&#x9;&#x9; , GroupID&#xa;&#x9;&#x9; , GroupDate&#xa;&#x9;&#x9; , UniqueDate&#xa;&#x9;&#x9; , MAX(UniqueDate) OVER (PARTITION BY GroupID) AS maxUniqueDate&#xa;&#x9;FROM #Example&#xa;&#x9;) calc_maxUD&#xa;WHERE maxUniqueDate &gt; GroupDate&#xa;&#x9;AND maxUniqueDate = UniqueDate" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.875755" StatementEstRows="547.723" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x2E519B513AF35D25" QueryPlanHash="0xE642BAE576C4671C" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="130"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" MemoryGrant="1920" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="32" CompileTime="15" CompileCPU="15" CompileMemory="200"><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="1920" RequiredMemory="512" DesiredMemory="1920" RequestedMemory="1920" GrantWaitTime="0" GrantedMemory="1920" MaxUsedMemory="704" MaxQueryMemory="584480"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1707144"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="31" CpuTime="31"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="547.723" EstimateIO="0" EstimateCPU="0.0088" AvgRowSize="31" EstimatedTotalSubtreeCost="0.875755" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupDate"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueDate"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="58" Batches="0" ActualExecutionMode="Row" ActualElapsedms="31" ActualCPUms="31" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="10000" EstimateIO="0" EstimateCPU="0.159298" AvgRowSize="39" EstimatedTotalSubtreeCost="0.866955" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupDate"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueDate"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="15" ActualCPUms="15" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><RelOp NodeId="2" PhysicalOp="Table Spool" LogicalOp="Lazy Spool" EstimateRows="2000" EstimateIO="0" EstimateCPU="0" AvgRowSize="31" EstimatedTotalSubtreeCost="0.701451" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupDate"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueDate"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2001" Batches="0" ActualExecutionMode="Row" ActualElapsedms="15" ActualCPUms="15" ActualScans="1" ActualLogicalReads="20001" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Spool><RelOp NodeId="3" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="10000" EstimateIO="0" EstimateCPU="0.020688" AvgRowSize="31" EstimatedTotalSubtreeCost="0.680763" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupDate"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueDate"></ColumnReference><ColumnReference Column="Segment1004"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupID"></ColumnReference></GroupBy><SegmentColumn><ColumnReference Column="Segment1004"></ColumnReference></SegmentColumn><RelOp NodeId="4" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="10000" EstimateIO="0.0112613" EstimateCPU="0.60342" AvgRowSize="31" EstimatedTotalSubtreeCost="0.660075" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupDate"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueDate"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" InputMemoryGrant="1920" OutputMemoryGrant="1536" UsedMemoryGrant="704"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupID"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="5" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="10000" EstimatedRowsRead="10000" EstimateIO="0.0342361" EstimateCPU="0.011157" AvgRowSize="31" EstimatedTotalSubtreeCost="0.0453931" TableCardinality="10000" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupDate"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueDate"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="43" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="10000" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupDate"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueDate"></ColumnReference></DefinedValue></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#Example]" IndexKind="Heap" Storage="RowStore"></Object></TableScan></RelOp></Sort></RelOp></Segment></RelOp></Spool></RelOp><RelOp NodeId="6" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="5" EstimateIO="0" EstimateCPU="0.0020688" AvgRowSize="31" EstimatedTotalSubtreeCost="0.0041376" Parallel="0" EstimateRebinds="2000" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupDate"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueDate"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="2001" ActualExecutions="2001"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><Predicate><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator></Predicate><RelOp NodeId="7" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="0.0020688" AvgRowSize="31" EstimatedTotalSubtreeCost="0.0020688" Parallel="0" EstimateRebinds="2000" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="2001" ActualExecutions="2001"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="MAX([tempdb].[dbo].[#Example].[UniqueDate])"><Aggregate Distinct="0" AggType="MAX"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueDate"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="8" PhysicalOp="Table Spool" LogicalOp="Lazy Spool" EstimateRows="5" EstimateIO="0" EstimateCPU="0" AvgRowSize="31" EstimatedTotalSubtreeCost="0" Parallel="0" EstimateRebinds="2000" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupDate"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueDate"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="2000" ActualEndOfScans="2001" ActualExecutions="2001"></RunTimeCountersPerThread></RunTimeInformation><Spool PrimaryNodeId="2"></Spool></RelOp></StreamAggregate></RelOp><RelOp NodeId="9" PhysicalOp="Table Spool" LogicalOp="Lazy Spool" EstimateRows="5" EstimateIO="0" EstimateCPU="0" AvgRowSize="31" EstimatedTotalSubtreeCost="0" Parallel="0" EstimateRebinds="2000" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupDate"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueDate"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="0" ActualRewinds="2000" ActualEndOfScans="2000" ActualExecutions="2000"></RunTimeCountersPerThread></RunTimeInformation><Spool PrimaryNodeId="2"></Spool></RelOp></NestedLoops></RelOp></NestedLoops></RelOp><Predicate><ScalarOperator ScalarString="[Expr1003]&gt;[tempdb].[dbo].[#Example].[GroupDate] AND [Expr1003]=[tempdb].[dbo].[#Example].[UniqueDate]"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="GT"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupDate"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueDate"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></Filter></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
Table 'Worktable'. Scan count 3, logical reads 28001, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#Example____________________________________________________________________________________________________________0000000000CB'. Scan count 1, logical reads 43, 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 = 31 ms, elapsed time = 31 ms.
 hidden batch(es)


set statistics time, io, xml on; with cte as ( select top 1 with ties UniqueID , GroupID , GroupDate , UniqueDate from #Example order by row_number() over (partition by GroupId order by UniqueDate desc) ) select * from cte where UniqueDate > GroupDate
UniqueID GroupID GroupDate UniqueDate
172 175 05/10/2017 20:43:09 07/10/2017 20:43:09
260 260 29/12/2017 20:43:09 03/01/2018 20:43:09
431 435 22/06/2018 20:43:09 23/06/2018 20:43:09
519 520 15/09/2018 20:43:09 19/09/2018 20:43:09
778 780 02/06/2019 20:43:09 05/06/2019 20:43:09
1037 1040 17/02/2020 20:43:09 19/02/2020 20:43:09
1125 1125 12/05/2020 20:43:09 17/05/2020 20:43:09
1296 1300 03/11/2020 20:43:09 04/11/2020 20:43:09
1384 1385 27/01/2021 20:43:09 31/01/2021 20:43:09
1643 1645 14/10/2021 20:43:09 17/10/2021 20:43:09
1902 1905 01/07/2022 20:43:09 03/07/2022 20:43:09
1990 1990 24/09/2022 20:43:09 29/09/2022 20:43:09
2161 2165 18/03/2023 20:43:09 19/03/2023 20:43:09
2249 2250 11/06/2023 20:43:09 15/06/2023 20:43:09
2508 2510 26/02/2024 20:43:09 29/02/2024 20:43:09
2767 2770 12/11/2024 20:43:09 14/11/2024 20:43:09
2855 2855 05/02/2025 20:43:09 10/02/2025 20:43:09
3026 3030 30/07/2025 20:43:09 31/07/2025 20:43:09
3114 3115 23/10/2025 20:43:09 27/10/2025 20:43:09
3373 3375 10/07/2026 20:43:09 13/07/2026 20:43:09
3632 3635 27/03/2027 20:43:09 29/03/2027 20:43:09
3720 3720 20/06/2027 20:43:09 25/06/2027 20:43:09
3891 3895 12/12/2027 20:43:09 13/12/2027 20:43:09
3979 3980 06/03/2028 20:43:09 10/03/2028 20:43:09
4238 4240 21/11/2028 20:43:09 24/11/2028 20:43:09
4497 4500 08/08/2029 20:43:09 10/08/2029 20:43:09
4585 4585 01/11/2029 20:43:09 06/11/2029 20:43:09
4756 4760 25/04/2030 20:43:09 26/04/2030 20:43:09
4844 4845 19/07/2030 20:43:09 23/07/2030 20:43:09
5103 5105 05/04/2031 20:43:09 08/04/2031 20:43:09
5362 5365 21/12/2031 20:43:09 23/12/2031 20:43:09
5450 5450 15/03/2032 20:43:09 20/03/2032 20:43:09
5621 5625 06/09/2032 20:43:09 07/09/2032 20:43:09
5709 5710 30/11/2032 20:43:09 04/12/2032 20:43:09
5968 5970 17/08/2033 20:43:09 20/08/2033 20:43:09
6227 6230 04/05/2034 20:43:09 06/05/2034 20:43:09
6315 6315 28/07/2034 20:43:09 02/08/2034 20:43:09
6486 6490 19/01/2035 20:43:09 20/01/2035 20:43:09
6574 6575 14/04/2035 20:43:09 18/04/2035 20:43:09
6833 6835 30/12/2035 20:43:09 02/01/2036 20:43:09
7092 7095 15/09/2036 20:43:09 17/09/2036 20:43:09
7180 7180 09/12/2036 20:43:09 14/12/2036 20:43:09
7351 7355 02/06/2037 20:43:09 03/06/2037 20:43:09
7439 7440 26/08/2037 20:43:09 30/08/2037 20:43:09
7698 7700 13/05/2038 20:43:09 16/05/2038 20:43:09
7957 7960 28/01/2039 20:43:09 30/01/2039 20:43:09
8045 8045 23/04/2039 20:43:09 28/04/2039 20:43:09
8216 8220 15/10/2039 20:43:09 16/10/2039 20:43:09
8304 8305 08/01/2040 20:43:09 12/01/2040 20:43:09
8563 8565 24/09/2040 20:43:09 27/09/2040 20:43:09
8822 8825 11/06/2041 20:43:09 13/06/2041 20:43:09
8910 8910 04/09/2041 20:43:09 09/09/2041 20:43:09
9081 9085 26/02/2042 20:43:09 27/02/2042 20:43:09
9169 9170 22/05/2042 20:43:09 26/05/2042 20:43:09
9428 9430 06/02/2043 20:43:09 09/02/2043 20:43:09
9687 9690 24/10/2043 20:43:09 26/10/2043 20:43:09
9775 9775 17/01/2044 20:43:09 22/01/2044 20:43:09
9946 9950 10/07/2044 20:43:09 11/07/2044 20:43:09
Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.5" Build="13.0.4001.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="with cte as (&#xa; select top 1 with ties &#xa; UniqueID&#xa; , GroupID&#xa; , GroupDate&#xa; , UniqueDate&#xa; from #Example&#xa; order by row_number() over (partition by GroupId order by UniqueDate desc)&#xa;)&#xa;select *&#xa;from cte&#xa;where UniqueDate &gt; GroupDate" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="1.28156" StatementEstRows="3000" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x7C4B9EB5E987B0D0" QueryPlanHash="0x1EB7AB8EFF4C9684" CardinalityEstimationModelVersion="130"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" MemoryGrant="3616" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="32" CompileTime="0" CompileCPU="0" CompileMemory="176"><MemoryGrantInfo SerialRequiredMemory="640" SerialDesiredMemory="3616" RequiredMemory="640" DesiredMemory="3616" RequestedMemory="3616" GrantWaitTime="0" GrantedMemory="3616" MaxUsedMemory="1472" MaxQueryMemory="584480"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1707144"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="15" CpuTime="15"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="3000" EstimateIO="0" EstimateCPU="0.0048" AvgRowSize="31" EstimatedTotalSubtreeCost="1.28156" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupDate"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueDate"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="58" Batches="0" ActualExecutionMode="Row" ActualElapsedms="15" ActualCPUms="15" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="1" PhysicalOp="Top" LogicalOp="Top" EstimateRows="10000" EstimateIO="0" EstimateCPU="0.001" AvgRowSize="31" EstimatedTotalSubtreeCost="1.27676" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupDate"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueDate"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="15" ActualCPUms="15" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Top RowCount="0" IsPercent="0" WithTies="1"><TieColumns><ColumnReference Column="Expr1003"></ColumnReference></TieColumns><TopExpression><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator></TopExpression><RelOp NodeId="2" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="10000" EstimateIO="0.0112613" EstimateCPU="0.60342" AvgRowSize="39" EstimatedTotalSubtreeCost="1.27576" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupDate"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueDate"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><MemoryFractions Input="0.526882" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2001" Batches="0" ActualExecutionMode="Row" ActualElapsedms="15" ActualCPUms="15" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="0" ActualExecutions="1" InputMemoryGrant="2080" OutputMemoryGrant="3104" UsedMemoryGrant="776"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Column="Expr1003"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="3" PhysicalOp="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="10000" EstimateIO="0" EstimateCPU="0.0008" AvgRowSize="39" EstimatedTotalSubtreeCost="0.661075" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupDate"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueDate"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="15" ActualCPUms="15" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><SequenceProject><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="row_number"><Sequence FunctionName="row_number"></Sequence></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="4" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="10000" EstimateIO="0" EstimateCPU="0.0002" AvgRowSize="39" EstimatedTotalSubtreeCost="0.660275" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupDate"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueDate"></ColumnReference><ColumnReference Column="Segment1004"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="15" ActualCPUms="15" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupID"></ColumnReference></GroupBy><SegmentColumn><ColumnReference Column="Segment1004"></ColumnReference></SegmentColumn><RelOp NodeId="5" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="10000" EstimateIO="0.0112613" EstimateCPU="0.60342" AvgRowSize="31" EstimatedTotalSubtreeCost="0.660075" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupDate"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueDate"></ColumnReference></OutputList><MemoryFractions Input="1" Output="0.473118"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="15" ActualCPUms="15" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" InputMemoryGrant="3488" OutputMemoryGrant="1536" UsedMemoryGrant="704"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupID"></ColumnReference></OrderByColumn><OrderByColumn Ascending="0"><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueDate"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="6" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="10000" EstimatedRowsRead="10000" EstimateIO="0.0342361" EstimateCPU="0.011157" AvgRowSize="31" EstimatedTotalSubtreeCost="0.0453931" TableCardinality="10000" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupDate"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueDate"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="43" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="10000" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupDate"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueDate"></ColumnReference></DefinedValue></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#Example]" IndexKind="Heap" Storage="RowStore"></Object></TableScan></RelOp></Sort></RelOp></Segment></RelOp></SequenceProject></RelOp></Sort></RelOp></Top></RelOp><Predicate><ScalarOperator ScalarString="[tempdb].[dbo].[#Example].[UniqueDate]&gt;[tempdb].[dbo].[#Example].[GroupDate]"><Compare CompareOp="GT"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="UniqueDate"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Example]" Column="GroupDate"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#Example____________________________________________________________________________________________________________0000000000CB'. Scan count 1, logical reads 43, 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 = 16 ms, elapsed time = 15 ms.
 hidden batch(es)