By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DROP TABLE IF EXISTS
dbo.Example;
CREATE TABLE dbo.Example
(
SupergroupKey integer NOT NULL
DEFAULT 0,
GroupKey integer NOT NULL,
RecordKey varchar(12) NOT NULL,
CONSTRAINT iExample
PRIMARY KEY CLUSTERED
(GroupKey ASC, RecordKey ASC),
CONSTRAINT [IX dbo.Example RecordKey, GroupKey]
UNIQUE NONCLUSTERED (RecordKey, GroupKey),
INDEX [IX dbo.Example SupergroupKey, GroupKey]
(SupergroupKey ASC, GroupKey ASC)
);
INSERT dbo.Example
(GroupKey, RecordKey)
VALUES
(1, 'Archimedes'),
(1, 'Newton'),
(1, 'Euler'),
(2, 'Euler'),
(2, 'Gauss'),
(3, 'Gauss'),
(3, 'Poincaré'),
(4, 'Ramanujan'),
(5, 'Neumann'),
(5, 'Grothendieck'),
(6, 'Grothendieck'),
(6, 'Tao');
12 rows affected
-- No execution plans or rows affected messages
SET NOCOUNT ON;
SET STATISTICS XML OFF;
-- Reset all supergroups
UPDATE E
SET SupergroupKey = 0
FROM dbo.Example AS E
WITH (TABLOCKX)
WHERE
SupergroupKey != 0;
DECLARE
@CurrentSupergroup integer = 0,
@CurrentGroup integer = 0;
WHILE 1 = 1
BEGIN
-- Next super group
SET @CurrentSupergroup += 1;
-- Find the lowest unprocessed group key
SELECT
@CurrentGroup = MIN(E.GroupKey)
FROM dbo.Example AS E
WHERE
E.SupergroupKey = 0;
-- Exit when no more unprocessed groups
IF @CurrentGroup IS NULL BREAK;
-- Set super group for all records in the current group
UPDATE E
SET E.SupergroupKey = @CurrentSupergroup
FROM dbo.Example AS E
WHERE
| SupergroupKey | GroupKey | RecordKey |
|---|---|---|
| 1 | 1 | Archimedes |
| 1 | 1 | Euler |
| 1 | 1 | Newton |
| 1 | 2 | Euler |
| 1 | 2 | Gauss |
| 1 | 3 | Gauss |
| 1 | 3 | Poincaré |
| 2 | 4 | Ramanujan |
| 3 | 5 | Grothendieck |
| 3 | 5 | Neumann |
| 3 | 6 | Grothendieck |
| 3 | 6 | Tao |