September 15, 2008 at 11:27 pm
Matt Miller (9/8/2008)
It sounds like you have a working solution, but if you want to pursue tracking down the scenarios it doesn't work in, let me know what an example might be. I already have one idea that might be causing an issue.
Hi Matt,
Sorry for the very late reply as I was on vacation. Anyway, please find the following scenarios with its respective output:
CREATE TABLE MyTable
(
RowID bigint,
RowKey int,
Locale varchar(10),
Descr nvarchar(1000)
)
/**
* Desc: First Scenario
* Output: 8,12,16
**/
INSERT INTO MyTable (RowID,RowKey,Locale,Descr)
SELECT '1','5','en','Sample Description 1' UNION ALL
SELECT '2','5','de','Sample Description 2' UNION ALL
SELECT '3','4','en','Sample Description 3' UNION ALL
SELECT '4','3','en','Sample Description 4' UNION ALL
SELECT '5','2','en','Sample Description 5' UNION ALL
SELECT '6','1','en','Sample Description 6' UNION ALL
SELECT '7','1','de','Sample Description 7' UNION ALL
SELECT '8','2','en','Sample Description 8' UNION ALL
SELECT '9','3','en','Sample Description 9' UNION ALL
SELECT '10','5','en','Sample Description 10' UNION ALL
SELECT '11','4','en','Sample Description 11' UNION ALL
SELECT '12','3','en','Sample Description 12' UNION ALL
SELECT '13','2','en','Sample Description 13' UNION ALL
SELECT '14','1','en','Sample Description 14' UNION ALL
SELECT '15','3','de','Sample Description 15'
/**
* Desc: Second Scenario
* Output: 2,3,6,9,11,12,14,16
**/
INSERT INTO MyTable (RowID,RowKey,Locale,Descr)
SELECT '1','1','en','Sample Description 1' UNION ALL
SELECT '2','1','en','Sample Description 2' UNION ALL
SELECT '3','1','en','Sample Description 3' UNION ALL
SELECT '4','2','en','Sample Description 4' UNION ALL
SELECT '5','2','de','Sample Description 5' UNION ALL
SELECT '6','2','en','Sample Description 6' UNION ALL
SELECT '7','3','de','Sample Description 7' UNION ALL
SELECT '8','3','en','Sample Description 8' UNION ALL
SELECT '9','3','en','Sample Description 9' UNION ALL
SELECT '10','4','en','Sample Description 10' UNION ALL
SELECT '11','4','en','Sample Description 11' UNION ALL
SELECT '12','4','en','Sample Description 12' UNION ALL
SELECT '13','5','en','Sample Description 13' UNION ALL
SELECT '14','5','en','Sample Description 14' UNION ALL
SELECT '15','5','de','Sample Description 15'
/**
* Desc: Third Scenario
* Output: 6,9,10,11,13,16
**/
INSERT INTO MyTable (RowID,RowKey,Locale,Descr)
SELECT '1','1','en','Sample Description 1' UNION ALL
SELECT '2','2','en','Sample Description 2' UNION ALL
SELECT '3','3','en','Sample Description 3' UNION ALL
SELECT '4','4','en','Sample Description 4' UNION ALL
SELECT '5','5','de','Sample Description 5' UNION ALL
SELECT '6','1','en','Sample Description 6' UNION ALL
SELECT '7','2','de','Sample Description 7' UNION ALL
SELECT '8','3','en','Sample Description 8' UNION ALL
SELECT '9','3','en','Sample Description 9' UNION ALL
SELECT '10','3','en','Sample Description 10' UNION ALL
SELECT '11','3','en','Sample Description 11' UNION ALL
SELECT '12','4','en','Sample Description 12' UNION ALL
SELECT '13','4','en','Sample Description 13' UNION ALL
SELECT '14','5','en','Sample Description 14' UNION ALL
SELECT '15','5','de','Sample Description 15'
/**
* Desc: Fourth Scenario
* Output: 4,9,10,11,13,16
**/
INSERT INTO MyTable (RowID,RowKey,Locale,Descr)
SELECT '1','1','en','Sample Description 1' UNION ALL
SELECT '2','2','en','Sample Description 2' UNION ALL
SELECT '3','3','en','Sample Description 3' UNION ALL
SELECT '4','1','en','Sample Description 4' UNION ALL
SELECT '5','2','de','Sample Description 5' UNION ALL
SELECT '6','3','en','Sample Description 6' UNION ALL
SELECT '7','1','de','Sample Description 7' UNION ALL
SELECT '8','2','en','Sample Description 8' UNION ALL
SELECT '9','3','en','Sample Description 9' UNION ALL
SELECT '10','3','en','Sample Description 10' UNION ALL
SELECT '11','3','en','Sample Description 11' UNION ALL
SELECT '12','1','en','Sample Description 12' UNION ALL
SELECT '13','1','en','Sample Description 13' UNION ALL
SELECT '14','2','en','Sample Description 14' UNION ALL
SELECT '15','3','de','Sample Description 15'
/**
* Desc: Fifth Scenario
* Output: 6,11,12,13,16
**/
INSERT INTO MyTable (RowID,RowKey,Locale,Descr)
SELECT '1','5','en','Sample Description 1' UNION ALL
SELECT '2','4','en','Sample Description 2' UNION ALL
SELECT '3','3','en','Sample Description 3' UNION ALL
SELECT '4','2','en','Sample Description 4' UNION ALL
SELECT '5','1','de','Sample Description 5' UNION ALL
SELECT '6','5','en','Sample Description 6' UNION ALL
SELECT '7','4','de','Sample Description 7' UNION ALL
SELECT '8','3','en','Sample Description 8' UNION ALL
SELECT '9','2','en','Sample Description 9' UNION ALL
SELECT '10','1','en','Sample Description 10' UNION ALL
SELECT '11','1','en','Sample Description 11' UNION ALL
SELECT '12','1','en','Sample Description 12' UNION ALL
SELECT '13','1','en','Sample Description 13' UNION ALL
SELECT '14','2','en','Sample Description 14' UNION ALL
SELECT '15','3','de','Sample Description 15'
Thanks for your time and help.
-- dans
September 16, 2008 at 7:00 am
Here is a CURSOR solution that scales excellent.
10 times the sample data, 10 times the time.
SET NOCOUNT ON
CREATE TABLE#Source
(
RowID INT PRIMARY KEY CLUSTERED,
RowKey INT,
Locale VARCHAR(10)
)
INSERT#Source
SELECT 1, 5, 'en' UNION ALL
SELECT 2, 4, 'en' UNION ALL
SELECT 3, 3, 'en' UNION ALL
SELECT 4, 2, 'en' UNION ALL
SELECT 5, 1, 'de' UNION ALL
SELECT 6, 5, 'en' UNION ALL
SELECT 7, 4, 'de' UNION ALL
SELECT 8, 3, 'en' UNION ALL
SELECT 9, 2, 'en' UNION ALL
SELECT10, 1, 'en' UNION ALL
SELECT11, 1, 'en' UNION ALL
SELECT14, 1, 'en' UNION ALL
SELECT15, 1, 'en' UNION ALL
SELECT16, 2, 'en' UNION ALL
SELECT17, 3, 'de'
DECLARE@Output TABLE
(
RowID INT
)
DECLARE@CurrID INT,
@PrevID INT,
@RowKey INT,
@Locale VARCHAR(10)
SET@PrevID = 1
DECLAREcurYak CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY FOR
SELECTRowID,
RowKey,
Locale
FROM#Source
ORDER BYRowID
OPENcurYak
FETCH NEXT FROMcurYak
INTO @CurrID, @RowKey, @Locale
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS(
SELECT*
FROM#Source
WHERERowID >= @PrevID
AND RowID < @CurrID
AND RowKey = @RowKey
AND Locale = @Locale
)
BEGIN
INSERT@Output
SELECT@CurrID
SET@PrevID = @CurrID
END
FETCH NEXT FROMcurYak
INTO @CurrID, @RowKey, @Locale
END
CLOSEcurYak
DEALLOCATEcurYak
SELECTRowID
FROM@Output
UNION ALL
SELECTMAX(RowID) + 1
FROM#Source
DROP TABLE#Source
N 56°04'39.16"
E 12°55'05.25"
Viewing 2 posts - 31 through 31 (of 31 total)
You must be logged in to reply to this topic. Login to reply