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. 1044276 fiddles created (9443 in the last week).

CREATE TABLE #Example ( UniqueID INT IDENTITY(1,1) , GroupID INT , GroupDate DATETIME , UniqueDate DATETIME ) CREATE CLUSTERED INDEX [CX_1] ON [#Example] ( [UniqueID] ASC ) 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 CREATE NONCLUSTERED INDEX [IX_2_4_3] ON [#Example] ( [GroupID] ASC, [UniqueDate] ASC, [GroupDate] ASC ) INCLUDE ([UniqueID]) -- 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 DROP TABLE #Example
UniqueID GroupID GroupDate UniqueDate
172 175 05/10/2017 19:28:46 07/10/2017 19:28:46
260 260 29/12/2017 19:28:46 03/01/2018 19:28:46
431 435 22/06/2018 19:28:46 23/06/2018 19:28:46
519 520 15/09/2018 19:28:46 19/09/2018 19:28:46
778 780 02/06/2019 19:28:46 05/06/2019 19:28:46
1037 1040 17/02/2020 19:28:46 19/02/2020 19:28:46
1125 1125 12/05/2020 19:28:46 17/05/2020 19:28:46
1296 1300 03/11/2020 19:28:46 04/11/2020 19:28:46
1384 1385 27/01/2021 19:28:46 31/01/2021 19:28:46
1643 1645 14/10/2021 19:28:46 17/10/2021 19:28:46
1902 1905 01/07/2022 19:28:46 03/07/2022 19:28:46
1990 1990 24/09/2022 19:28:46 29/09/2022 19:28:46
2161 2165 18/03/2023 19:28:46 19/03/2023 19:28:46
2249 2250 11/06/2023 19:28:46 15/06/2023 19:28:46
2508 2510 26/02/2024 19:28:46 29/02/2024 19:28:46
2767 2770 12/11/2024 19:28:46 14/11/2024 19:28:46
2855 2855 05/02/2025 19:28:46 10/02/2025 19:28:46
3026 3030 30/07/2025 19:28:46 31/07/2025 19:28:46
3114 3115 23/10/2025 19:28:46 27/10/2025 19:28:46
3373 3375 10/07/2026 19:28:46 13/07/2026 19:28:46
3632 3635 27/03/2027 19:28:46 29/03/2027 19:28:46
3720 3720 20/06/2027 19:28:46 25/06/2027 19:28:46
3891 3895 12/12/2027 19:28:46 13/12/2027 19:28:46
3979 3980 06/03/2028 19:28:46 10/03/2028 19:28:46
4238 4240 21/11/2028 19:28:46 24/11/2028 19:28:46
4497 4500 08/08/2029 19:28:46 10/08/2029 19:28:46
4585 4585 01/11/2029 19:28:46 06/11/2029 19:28:46
4756 4760 25/04/2030 19:28:46 26/04/2030 19:28:46
4844 4845 19/07/2030 19:28:46 23/07/2030 19:28:46
5103 5105 05/04/2031 19:28:46 08/04/2031 19:28:46
5362 5365 21/12/2031 19:28:46 23/12/2031 19:28:46
5450 5450 15/03/2032 19:28:46 20/03/2032 19:28:46
5621 5625 06/09/2032 19:28:46 07/09/2032 19:28:46
5709 5710 30/11/2032 19:28:46 04/12/2032 19:28:46
5968 5970 17/08/2033 19:28:46 20/08/2033 19:28:46
6227 6230 04/05/2034 19:28:46 06/05/2034 19:28:46
6315 6315 28/07/2034 19:28:46 02/08/2034 19:28:46
6486 6490 19/01/2035 19:28:46 20/01/2035 19:28:46
6574 6575 14/04/2035 19:28:46 18/04/2035 19:28:46
6833 6835 30/12/2035 19:28:46 02/01/2036 19:28:46
7092 7095 15/09/2036 19:28:46 17/09/2036 19:28:46
7180 7180 09/12/2036 19:28:46 14/12/2036 19:28:46
7351 7355 02/06/2037 19:28:46 03/06/2037 19:28:46
7439 7440 26/08/2037 19:28:46 30/08/2037 19:28:46
7698 7700 13/05/2038 19:28:46 16/05/2038 19:28:46
7957 7960 28/01/2039 19:28:47 30/01/2039 19:28:47
8045 8045 23/04/2039 19:28:47 28/04/2039 19:28:47
8216 8220 15/10/2039 19:28:47 16/10/2039 19:28:47
8304 8305 08/01/2040 19:28:47 12/01/2040 19:28:47
8563 8565 24/09/2040 19:28:47 27/09/2040 19:28:47
8822 8825 11/06/2041 19:28:47 13/06/2041 19:28:47
8910 8910 04/09/2041 19:28:47 09/09/2041 19:28:47
9081 9085 26/02/2042 19:28:47 27/02/2042 19:28:47
9169 9170 22/05/2042 19:28:47 26/05/2042 19:28:47
9428 9430 06/02/2043 19:28:47 09/02/2043 19:28:47
9687 9690 24/10/2043 19:28:47 26/10/2043 19:28:47
9775 9775 17/01/2044 19:28:47 22/01/2044 19:28:47
9946 9950 10/07/2044 19:28:47 11/07/2044 19:28:47
 hidden batch(es)