February 14, 2011 at 9:26 am
My solution runs in about 400 milliseconds using 164k reads.
It produces the 163 ID's from the 64,178 record sample set.
SET NOCOUNT ON
-- Solution here
CREATE TABLE #Values
(
idMvt INT PRIMARY KEY CLUSTERED
)
INSERT #Values
(
idMvt
)
SELECT DISTINCT u.idMvt
FROM dbo.Mvts AS s
UNPIVOT (
idMvt
FOR Col IN (s.idMvt1, s.idMvt2, s.idMvt3)
) AS u
CREATE TABLE #Result
(
ID INT PRIMARY KEY CLUSTERED
)
DECLARE @CurrID INT,
@OldID INT,
@idMvt1 INT,
@idMvt2 INT,
@idMvt3 INT
SELECT TOP(1) @CurrID = ID,
@idMvt1 = idMvt1,
@idMvt2 = idMvt2,
@idMvt3 = idMvt3
FROM dbo.Mvts
ORDER BY ID
INSERT #Result
(
ID
)
VALUES (
@CurrID
)
DELETE
FROM #Values
WHERE idMvt IN (@idMvt1, @idMvt2, @idMvt3)
WHILE @CurrID IS NOT NULL
BEGIN
SELECT @OldID = @CurrID,
@CurrID = NULL
SELECT TOP(1) @CurrID = s.ID,
@idMvt1 = s.idMvt1,
@idMvt2 = s.idMvt2,
@idMvt3 = s.idMvt3
FROM dbo.Mvts AS s
WHERE EXISTS(SELECT * FROM #Values AS v WHERE v.idMvt = s.idMvt1)
AND EXISTS(SELECT * FROM #Values AS v WHERE v.idMvt = s.idMvt2)
AND EXISTS(SELECT * FROM #Values AS v WHERE v.idMvt = s.idMvt3)
AND s.ID > @OldID
ORDER BY s.ID
IF @CurrID IS NOT NULL
BEGIN
INSERT #Result
(
ID
)
VALUES (
@CurrID
)
DELETE
FROM #Values
WHERE idMvt IN (@idMvt1, @idMvt2, @idMvt3)
END
END
SELECT ID
FROM #Result
DROP TABLE #Values,
#Result
N 56°04'39.16"
E 12°55'05.25"
Viewing post 76 (of 75 total)
You must be logged in to reply to this topic. Login to reply