Get duplicate range - How to

Question

Get duplicate range - How to

dwither_07

SSC-Addicted

Points: 496
More actions
August 10, 2008 at 7:56 am

#211053

Hi all,
I'm new to ms sql server, can someone tell me on how to get the following range results from the given sample data on a certain table using Stored Procedure.
Lets I have the following data on Promo table...
row_id promo_id desc
1 1 desc1
2 1 desc2
3 2 desc3
4 3 desc4
5 4 desc5
6 1 desc6
7 2 desc7
the result should return the following row_id's range...
2, 6, 8
which means the range of id's to be processed should from 1-2, 2-6 and 6-8, last row should always be plus one (+1)
Another sample would be....if i have the following data on Promo table:
row_id promo_id desc
1 1 desc1
2 2 desc2
3 3 desc3
4 4 desc4
5 5 desc5
6 1 desc6
7 2 desc7
8 3 desc8
9 4 desc9
10 5 desc10
11 1 desc11
12 2 desc12
13 3 desc13
14 4 desc14
15 5 desc15
the result should return the following row_id's range:
6,11,16
which means the range is from 1-6, 6-11,11-16.
The idea here is to check the promo id with duplicate keys the first time it encounters.
any good ideas or sample code is highly appreciated.
thanks so much for your time.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004364 More actions · Answer 1

Howdy,

Since you're brand new, please take the time to read and follow the suggestions in the link in my signature... it'll help you get very high quality help much faster. 🙂

Here's a single solution that takes care of both sets of sample data you provided... it also gives an example of how we'd like to see data posted. The reason why we like to see it this way is in the link in my signature that I told you about above. Thanks.

--===== Create a demonstration table and populate it.

-- Note that this is NOT part of the solution!

CREATE TABLE #MyHead

(

Row_ID INT,

Promo_ID INT,

[Desc] VARCHAR(10)

)

--===== Populate the table with the first example data

INSERT INTO #MyHead

(Row_ID,Promo_ID,[Desc])

SELECT '1','1','desc1' UNION ALL

SELECT '2','1','desc2' UNION ALL

SELECT '3','2','desc3' UNION ALL

SELECT '4','3','desc4' UNION ALL

SELECT '5','4','desc5' UNION ALL

SELECT '6','1','desc6' UNION ALL

SELECT '7','2','desc7'

--===== Find all the places where the Promo_ID decreases

-- or stays the same...

SELECT hi.Row_ID

FROM #MyHead lo

INNER JOIN

#MyHead hi

ON lo.Row_ID+1 = hi.Row_ID

AND hi.Promo_ID <= lo.Promo_ID

UNION ALL

-- and throw in the final row that doesn't exist yet

SELECT MAX(Row_ID)+1

FROM #MyHead

--===== Populate the table with the second example data

TRUNCATE TABLE #MyHead

INSERT INTO #MyHead

(Row_ID,Promo_ID,[Desc])

SELECT '1','1','desc1' UNION ALL

SELECT '2','2','desc2' UNION ALL

SELECT '3','3','desc3' UNION ALL

SELECT '4','4','desc4' UNION ALL

SELECT '5','5','desc5' UNION ALL

SELECT '6','1','desc6' UNION ALL

SELECT '7','2','desc7' UNION ALL

SELECT '8','3','desc8' UNION ALL

SELECT '9','4','desc9' UNION ALL

SELECT '10','5','desc10' UNION ALL

SELECT '11','1','desc11' UNION ALL

SELECT '12','2','desc12' UNION ALL

SELECT '13','3','desc13' UNION ALL

SELECT '14','4','desc14' UNION ALL

SELECT '15','5','desc15'

--===== This is a repeat of the same code from above.

--===== Find all the places where the Promo_ID decreases

-- or stays the same...

SELECT hi.Row_ID

FROM #MyHead lo

INNER JOIN

#MyHead hi

ON lo.Row_ID+1 = hi.Row_ID

AND hi.Promo_ID <= lo.Promo_ID

UNION ALL

-- and throw in the final row that doesn't exist yet

SELECT MAX(Row_ID)+1

FROM #MyHead

DROP TABLE #MyHead

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dwither_07 SSC-Addicted Points: 496 More actions · Answer 2

Hello Jeff,

Thank so much for the great solution and prompt response. The link that you gave really was a great help for me to heads up on the proper posting. Anyway, I tested the script using the following scenarios below and there is one scenario that it gave me an additional result which is the "fifth data scenario".

--===== Populate the table with the second example data

CREATE TABLE #MyHead

(

Row_ID INT,

Promo_ID INT,

[Desc] VARCHAR(10)

)

--===== First Data Scenario

TRUNCATE TABLE #MyHead

INSERT INTO #MyHead

(Row_ID,Promo_ID,[Desc])

SELECT '1','1','desc1' UNION ALL

SELECT '2','1','desc2' UNION ALL

SELECT '3','2','desc3' UNION ALL

SELECT '4','3','desc4' UNION ALL

SELECT '5','4','desc5' UNION ALL

SELECT '6','1','desc6' UNION ALL

SELECT '7','2','desc7'

--== This should have an output 2,6,8

--===== Second Data Scenario

TRUNCATE TABLE #MyHead

INSERT INTO #MyHead

(Row_ID,Promo_ID,[Desc])

SELECT '1','1','desc1' UNION ALL

SELECT '2','2','desc2' UNION ALL

SELECT '3','3','desc3' UNION ALL

SELECT '4','4','desc4' UNION ALL

SELECT '5','5','desc5' UNION ALL

SELECT '6','1','desc6' UNION ALL

SELECT '7','2','desc7' UNION ALL

SELECT '8','3','desc8' UNION ALL

SELECT '9','4','desc9' UNION ALL

SELECT '10','5','desc10' UNION ALL

SELECT '11','1','desc11' UNION ALL

SELECT '12','2','desc12' UNION ALL

SELECT '13','3','desc13' UNION ALL

SELECT '14','4','desc14' UNION ALL

SELECT '15','5','desc15'

--== This should have an output 6,11,16

--===== Third Data Scenario

TRUNCATE TABLE #MyHead

INSERT INTO #MyHead

(Row_ID,Promo_ID,[Desc])

SELECT '1','1','desc1' UNION ALL

SELECT '2','1','desc2' UNION ALL

SELECT '3','1','desc3' UNION ALL

SELECT '4','1','desc4' UNION ALL

SELECT '5','1','desc5' UNION ALL

SELECT '6','2','desc6' UNION ALL

SELECT '7','2','desc7' UNION ALL

SELECT '8','2','desc8' UNION ALL

SELECT '9','2','desc9' UNION ALL

SELECT '10','2','desc10' UNION ALL

SELECT '11','3','desc11' UNION ALL

SELECT '12','3','desc12' UNION ALL

SELECT '13','3','desc13' UNION ALL

SELECT '14','3','desc14' UNION ALL

SELECT '15','3','desc15'

--== This should have an output 2,3,4,5,7,8,9,10,12,13,14,15,16

--===== Fourth Data Scenario

TRUNCATE TABLE #MyHead

INSERT INTO #MyHead

(Row_ID,Promo_ID,[Desc])

SELECT '1','1','desc1' UNION ALL

SELECT '2','2','desc2' UNION ALL

SELECT '3','3','desc3' UNION ALL

SELECT '4','4','desc4' UNION ALL

SELECT '5','5','desc5' UNION ALL

SELECT '6','1','desc6' UNION ALL

SELECT '7','2','desc7' UNION ALL

SELECT '8','3','desc8' UNION ALL

SELECT '9','4','desc9' UNION ALL

SELECT '10','5','desc10' UNION ALL

SELECT '11','1','desc11' UNION ALL

SELECT '12','1','desc12' UNION ALL

SELECT '13','1','desc13' UNION ALL

SELECT '14','1','desc14' UNION ALL

SELECT '15','1','desc15' UNION ALL

SELECT '16','2','desc16' UNION ALL

SELECT '17','2','desc17' UNION ALL

SELECT '18','2','desc18' UNION ALL

SELECT '19','2','desc19' UNION ALL

SELECT '20','2','desc20'

--== This should have an output 6,11,12,13,14,15,17,18,19,20,21

--===== Fifth Data Scenario

TRUNCATE TABLE #MyHead

INSERT INTO #MyHead

(Row_ID,Promo_ID,[Desc])

SELECT '1','1','desc1' UNION ALL

SELECT '2','2','desc2' UNION ALL

SELECT '3','3','desc3' UNION ALL

SELECT '4','4','desc4' UNION ALL

SELECT '5','5','desc5' UNION ALL

SELECT '6','6','desc6' UNION ALL

SELECT '7','6','desc7' UNION ALL

SELECT '8','6','desc8' UNION ALL

SELECT '9','6','desc9' UNION ALL

SELECT '10','6','desc10' UNION ALL

SELECT '11','1','desc11' UNION ALL

SELECT '12','2','desc12' UNION ALL

SELECT '13','3','desc13' UNION ALL

SELECT '14','4','desc14' UNION ALL

SELECT '15','5','desc15' UNION ALL

SELECT '16','1','desc16' UNION ALL

SELECT '17','2','desc17' UNION ALL

SELECT '18','3','desc18' UNION ALL

SELECT '19','4','desc19' UNION ALL

SELECT '20','5','desc20'

--== This should have an output 7,8,9,10,16,21

The script below outputted 7,8,9,10,11,16,21....which shouldn't be. If you tell me what the script does then that would be great, I just have a little idea about it 🙁

--===== Find all the places where the Promo_ID decreases or stays the same...

SELECT hi.Row_ID

FROM #MyHead lo

INNER JOIN

#MyHead hi

ON lo.Row_ID+1 = hi.Row_ID

AND hi.Promo_ID <= lo.Promo_ID

UNION ALL

-- and throw in the final row that doesn't exist yet

SELECT MAX(Row_ID)+1

FROM #MyHead

Really, thank you so much for your help Jeff.

Jeff Moden SSC Guru Points: 1004364 More actions · Answer 3

--===== First Data Scenario
TRUNCATE TABLE #MyHead
INSERT INTO #MyHead
(Row_ID,Promo_ID,[Desc])
SELECT '1','1','desc1' UNION ALL
SELECT '2','1','desc2' UNION ALL
SELECT '3','2','desc3' UNION ALL
SELECT '4','3','desc4' UNION ALL
SELECT '5','4','desc5' UNION ALL
SELECT '6','1','desc6' UNION ALL
SELECT '7','2','desc7'
--== This should have an output 2,3,4,5,7,8,9,10,12,13,14,15,16

Why? This one only goes up to 7... where did the 9 through 16 come from? As well as posting data, ya gotta be a bit clear about the rules?

The other ones need a bit of explanation as well... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dwither_07 SSC-Addicted Points: 496 More actions · Answer 4

Sorry for that one Jeff, I already edited my previous post since that was incorrect..I really apologize for that one. The correct scenarios with thier respective result is written below:

--===== Populate the table with the second example data

CREATE TABLE #MyHead

(

Row_ID INT,

Promo_ID INT,

[Desc] VARCHAR(10)

)

--===== First Data Scenario

TRUNCATE TABLE #MyHead

INSERT INTO #MyHead

(Row_ID,Promo_ID,[Desc])

SELECT '1','1','desc1' UNION ALL

SELECT '2','1','desc2' UNION ALL

SELECT '3','2','desc3' UNION ALL

SELECT '4','3','desc4' UNION ALL

SELECT '5','4','desc5' UNION ALL

SELECT '6','1','desc6' UNION ALL

SELECT '7','2','desc7'

--== This should have an output 2,6,8

--===== Second Data Scenario

TRUNCATE TABLE #MyHead

INSERT INTO #MyHead

(Row_ID,Promo_ID,[Desc])

SELECT '1','1','desc1' UNION ALL

SELECT '2','2','desc2' UNION ALL

SELECT '3','3','desc3' UNION ALL

SELECT '4','4','desc4' UNION ALL

SELECT '5','5','desc5' UNION ALL

SELECT '6','1','desc6' UNION ALL

SELECT '7','2','desc7' UNION ALL

SELECT '8','3','desc8' UNION ALL

SELECT '9','4','desc9' UNION ALL

SELECT '10','5','desc10' UNION ALL

SELECT '11','1','desc11' UNION ALL

SELECT '12','2','desc12' UNION ALL

SELECT '13','3','desc13' UNION ALL

SELECT '14','4','desc14' UNION ALL

SELECT '15','5','desc15'

--== This should have an output 6,11,16

--===== Third Data Scenario

TRUNCATE TABLE #MyHead

INSERT INTO #MyHead

(Row_ID,Promo_ID,[Desc])

SELECT '1','1','desc1' UNION ALL

SELECT '2','1','desc2' UNION ALL

SELECT '3','1','desc3' UNION ALL

SELECT '4','1','desc4' UNION ALL

SELECT '5','1','desc5' UNION ALL

SELECT '6','2','desc6' UNION ALL

SELECT '7','2','desc7' UNION ALL

SELECT '8','2','desc8' UNION ALL

SELECT '9','2','desc9' UNION ALL

SELECT '10','2','desc10' UNION ALL

SELECT '11','3','desc11' UNION ALL

SELECT '12','3','desc12' UNION ALL

SELECT '13','3','desc13' UNION ALL

SELECT '14','3','desc14' UNION ALL

SELECT '15','3','desc15'

--== This should have an output 2,3,4,5,7,8,9,10,12,13,14,15,16

--===== Fourth Data Scenario

TRUNCATE TABLE #MyHead

INSERT INTO #MyHead

(Row_ID,Promo_ID,[Desc])

SELECT '1','1','desc1' UNION ALL

SELECT '2','2','desc2' UNION ALL

SELECT '3','3','desc3' UNION ALL

SELECT '4','4','desc4' UNION ALL

SELECT '5','5','desc5' UNION ALL

SELECT '6','1','desc6' UNION ALL

SELECT '7','2','desc7' UNION ALL

SELECT '8','3','desc8' UNION ALL

SELECT '9','4','desc9' UNION ALL

SELECT '10','5','desc10' UNION ALL

SELECT '11','1','desc11' UNION ALL

SELECT '12','1','desc12' UNION ALL

SELECT '13','1','desc13' UNION ALL

SELECT '14','1','desc14' UNION ALL

SELECT '15','1','desc15' UNION ALL

SELECT '16','2','desc16' UNION ALL

SELECT '17','2','desc17' UNION ALL

SELECT '18','2','desc18' UNION ALL

SELECT '19','2','desc19' UNION ALL

SELECT '20','2','desc20'

--== This should have an output 6,11,12,13,14,15,17,18,19,20,21

--===== Fifth Data Scenario

TRUNCATE TABLE #MyHead

INSERT INTO #MyHead

(Row_ID,Promo_ID,[Desc])

SELECT '1','1','desc1' UNION ALL

SELECT '2','2','desc2' UNION ALL

SELECT '3','3','desc3' UNION ALL

SELECT '4','4','desc4' UNION ALL

SELECT '5','5','desc5' UNION ALL

SELECT '6','6','desc6' UNION ALL

SELECT '7','6','desc7' UNION ALL

SELECT '8','6','desc8' UNION ALL

SELECT '9','6','desc9' UNION ALL

SELECT '10','6','desc10' UNION ALL

SELECT '11','1','desc11' UNION ALL

SELECT '12','2','desc12' UNION ALL

SELECT '13','3','desc13' UNION ALL

SELECT '14','4','desc14' UNION ALL

SELECT '15','5','desc15' UNION ALL

SELECT '16','1','desc16' UNION ALL

SELECT '17','2','desc17' UNION ALL

SELECT '18','3','desc18' UNION ALL

SELECT '19','4','desc19' UNION ALL

SELECT '20','5','desc20'

--== This should have an output 7,8,9,10,16,21

--===== Sixth Data Scenario

TRUNCATE TABLE #MyHead

INSERT INTO #MyHead

(Row_ID,Promo_ID,[Desc])

SELECT '1','1','desc1' UNION ALL

SELECT '2','2','desc2' UNION ALL

SELECT '3','3','desc3' UNION ALL

SELECT '4','2','desc4' UNION ALL

SELECT '5','2','desc5' UNION ALL

SELECT '6','6','desc6' UNION ALL

SELECT '7','6','desc7' UNION ALL

SELECT '8','6','desc8' UNION ALL

SELECT '9','1','desc9'

--== This should output 4,5,7,8,10

The one listed above was just an example in where it is expected to have 1 million data with the given possible scenarios.

Thanks so much for your time

Jeff Moden SSC Guru Points: 1004364 More actions · Answer 5

Did you test them with the code I gave you because it solves the two originals I posted just exactly as you stated the results should be.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004364 More actions · Answer 6

Ok... I've tested all of your scenarios and they all came out like you said in the comments except the 5th one... and that's because you made a mistake... Row_ID 11 should also be included. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dwither_07 SSC-Addicted Points: 496 More actions · Answer 7

Yes I did and it gave the correct results for those scenarios that I gave. Sorry but I just come to think on another possible scenarios and tested the script.

--===== Fifth Data Scenario

TRUNCATE TABLE #MyHead

INSERT INTO #MyHead

(Row_ID,Promo_ID,[Desc])

SELECT '1','1','desc1' UNION ALL

SELECT '2','2','desc2' UNION ALL

SELECT '3','3','desc3' UNION ALL

SELECT '4','4','desc4' UNION ALL

SELECT '5','5','desc5' UNION ALL

SELECT '6','6','desc6' UNION ALL

SELECT '7','6','desc7' UNION ALL

SELECT '8','6','desc8' UNION ALL

SELECT '9','6','desc9' UNION ALL

SELECT '10','6','desc10' UNION ALL

SELECT '11','1','desc11' UNION ALL

SELECT '12','2','desc12' UNION ALL

SELECT '13','3','desc13' UNION ALL

SELECT '14','4','desc14' UNION ALL

SELECT '15','5','desc15' UNION ALL

SELECT '16','1','desc16' UNION ALL

SELECT '17','2','desc17' UNION ALL

SELECT '18','3','desc18' UNION ALL

SELECT '19','4','desc19' UNION ALL

SELECT '20','5','desc20'

--== This should have an output 7,8,9,10,16,21

--===== Sixth Data Scenario

TRUNCATE TABLE #MyHead

INSERT INTO #MyHead

(Row_ID,Promo_ID,[Desc])

SELECT '1','1','desc1' UNION ALL

SELECT '2','2','desc2' UNION ALL

SELECT '3','3','desc3' UNION ALL

SELECT '4','2','desc4' UNION ALL

SELECT '5','2','desc5' UNION ALL

SELECT '6','6','desc6' UNION ALL

SELECT '7','6','desc7' UNION ALL

SELECT '8','6','desc8' UNION ALL

SELECT '9','1','desc9'

--== This should output 4,5,7,8,10

On the "Fifth Data Scenario", it should output 7,8,9,10,16,21 but the script gave me 7,8,9,10,11,16,21 and on the "Sixth Data Scenario", it should output 4,5,7,8,10 but the script gave me 4,5,7,8,9,10. Sorry for not being clear about this.

thank you for the prompt response and great help.

Jeff Moden SSC Guru Points: 1004364 More actions · Answer 8

Um... no... according o the rules you gave, script 5 should include rowid # 11 because the promo-id went from 6 to 1.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dwither_07 SSC-Addicted Points: 496 More actions · Answer 9

Hmm..let me trace on how did I come up with the said result, I'll just use script number 6 (Sixth Data Scenario) since it has less data.

--===== Sixth Data Scenario

TRUNCATE TABLE #MyHead

INSERT INTO #MyHead

(Row_ID,Promo_ID,[Desc])

SELECT '1','1','desc1' UNION ALL

SELECT '2','2','desc2' UNION ALL

SELECT '3','3','desc3' UNION ALL

SELECT '4','2','desc4' UNION ALL

SELECT '5','2','desc5' UNION ALL

SELECT '6','6','desc6' UNION ALL

SELECT '7','6','desc7' UNION ALL

SELECT '8','6','desc8' UNION ALL

SELECT '9','1','desc9'

--== This should output 4,5,7,8,10

Just for tracing purposes, If I have two temp tables...lets say:

#temp_dup --> temporary storage on where i will put those

Promo_ID's that I've encountered without

duplicate.

#temp_result --> List of Row_ID's for the final result

Let say, I went through rows 1-3, since I haven't encountered a duplicate I would list the Promo_ID's on my #temp_dup and my #temp_dup will now contain Promo_ID's 1-3

#temp_dup (Promo_ID temp holder)

1

2

3

#temp_result (Row_ID range)

(blank)

If I continue to row 4, which has a Promo_ID 2 and by comparing to the values I have on #temp_dup..I can see that there's a duplicate (which is Promo_ID 2)..so I would list the Row_ID on my #temp_result temporary table and #temp_dup should be cleared and be replaced by the new value(it should always reset to zero if duplicate value is encountered). my temp tables should now contain:

#temp_dup (Promo_ID temp holder)

2

#temp_result (Row_ID range)

4

continue on row 5, which has a Promo_ID of 2 also and by comparing on my #temp_dup table which has also a Promo_ID of 2, so i can see there's a duplicate. So I will list the Row_id on my #temp_result. My temp tables will now contain:

#temp_dup (Promo_ID temp holder)

2

#temp_result (Row_ID range)

4

5

continue on row 6, since it has a different Promo_ID compared as listed on my #temp_dup, so no duplicates, then I would list that Promo_ID on my #temp_dup. My temp tables will now contain:

#temp_dup (Promo_ID temp holder)

2

6

#temp_result (Row_ID range)

4

5

to continue on row 7, which has a Promo_ID 6 and my #temp_dup has also Promo_ID 6, so I would list the Row_ID on my #temp_result and clear my #temp_dup table and insert a new value 6. My temp tables will now contain:

#temp_dup (Promo_ID temp holder)

6

#temp_result (Row_ID range)

4

5

7

On row 8, which also has a Promo_ID 6 and my #temp_dup has also Promo_ID 6, so I would then list the Row_ID on my #temp_result and clear my #temp_dup table and insert a new value of 6 still. My temp tables will now contain:

#temp_dup (Promo_ID temp holder)

6

#temp_result (Row_ID range)

4

5

7

8

On the last row (Row 9), it has a Promo_ID 1..so by comparing on my #temp_dup table, it doesn't have duplicate so I will just list it there then add the additional row 10 on my #temp_result. My temp tables will now contain:

#temp_dup (Promo_ID temp holder)

6

1

#temp_result (Row_ID range)

4

5

7

8

10

The one on my #temp_result would be the final one. (4,5,7,8,10). I hope this can help clear the logic on what I am about to achieve. Thank you very much for your help

sgmunson SSC Guru Points: 110640 More actions · Answer 10

Just my two cents, but the OP has consistently NOT provided the exact details of the rules on which the output is based, and instead keeps adding more detail as other scenarios occur to him/her. This really has to stop. It's unreasonable and rather disrespectful of the time that folks are spending trying to provide a solution when you don't provide a complete set of rules on which to base that solution. When one is asking for help, one needs to provide a COMPLETE statement of the problem.

And 2 cents more on the actual solution - it appears that the desired result is the ID's associated with every 2nd or greater occurrence of a given value for the Promo_Id field - all the duplicates, as it were - along with a final value that's 1 greater than the last ID of ANY record, duplicate or not. However, this isn't quite right on the basis of the desired output. Apparently, whenever you go backwards with Promo_ID, the count towards 2nd or greater starts at zero again. Without knowing the reasoning on WHY this is necessary, I'm not sure anyone should be spending time trying to solve this.

Dwither_07 - please provide more detail on EXACTLY what you're trying to accomplish, as the desired output doesn't make much sense, and a better solution might well be available if sufficient knowledge of the objective were available.

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1004364 More actions · Answer 11

dwither_07 (8/11/2008)
Hmm..let me trace on how did I come up with the said result, I'll just use script number 6 (Sixth Data Scenario) since it has less data.
--===== Sixth Data Scenario
TRUNCATE TABLE #MyHead
INSERT INTO #MyHead
(Row_ID,Promo_ID,[Desc])
SELECT '1','1','desc1' UNION ALL
SELECT '2','2','desc2' UNION ALL
SELECT '3','3','desc3' UNION ALL
SELECT '4','2','desc4' UNION ALL
SELECT '5','2','desc5' UNION ALL
SELECT '6','6','desc6' UNION ALL
SELECT '7','6','desc7' UNION ALL
SELECT '8','6','desc8' UNION ALL
SELECT '9','1','desc9'
--== This should output 4,5,7,8,10
Just for tracing purposes, If I have two temp tables...lets say:
#temp_dup --> temporary storage on where i will put those
Promo_ID's that I've encountered without
duplicate.
#temp_result --> List of Row_ID's for the final result
Let say, I went through rows 1-3, since I haven't encountered a duplicate I would list the Promo_ID's on my #temp_dup and my #temp_dup will now contain Promo_ID's 1-3
#temp_dup (Promo_ID temp holder)
1
2
3
#temp_result (Row_ID range)
(blank)
If I continue to row 4, which has a Promo_ID 2 and by comparing to the values I have on #temp_dup..I can see that there's a duplicate (which is Promo_ID 2)..so I would list the Row_ID on my #temp_result temporary table and #temp_dup should be cleared and be replaced by the new value(it should always reset to zero if duplicate value is encountered). my temp tables should now contain:
#temp_dup (Promo_ID temp holder)
2
#temp_result (Row_ID range)
4
continue on row 5, which has a Promo_ID of 2 also and by comparing on my #temp_dup table which has also a Promo_ID of 2, so i can see there's a duplicate. So I will list the Row_id on my #temp_result. My temp tables will now contain:
#temp_dup (Promo_ID temp holder)
2
#temp_result (Row_ID range)
4
5
continue on row 6, since it has a different Promo_ID compared as listed on my #temp_dup, so no duplicates, then I would list that Promo_ID on my #temp_dup. My temp tables will now contain:
#temp_dup (Promo_ID temp holder)
2
6
#temp_result (Row_ID range)
4
5
to continue on row 7, which has a Promo_ID 6 and my #temp_dup has also Promo_ID 6, so I would list the Row_ID on my #temp_result and clear my #temp_dup table and insert a new value 6. My temp tables will now contain:
#temp_dup (Promo_ID temp holder)
6
#temp_result (Row_ID range)
4
5
7
On row 8, which also has a Promo_ID 6 and my #temp_dup has also Promo_ID 6, so I would then list the Row_ID on my #temp_result and clear my #temp_dup table and insert a new value of 6 still. My temp tables will now contain:
#temp_dup (Promo_ID temp holder)
6
#temp_result (Row_ID range)
4
5
7
8
On the last row (Row 9), it has a Promo_ID 1..so by comparing on my #temp_dup table, it doesn't have duplicate so I will just list it there then add the additional row 10 on my #temp_result. My temp tables will now contain:
#temp_dup (Promo_ID temp holder)
6
1
#temp_result (Row_ID range)
4
5
7
8
10
The one on my #temp_result would be the final one. (4,5,7,8,10). I hope this can help clear the logic on what I am about to achieve. Thank you very much for your help

Then tell me how "6" is supposed to show up in Scenario 1.;)

Sorry... I'm all done here.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dwither_07 SSC-Addicted Points: 496 More actions · Answer 12

This is how I came up with the result on scenario 1.

--===== Populate the table with the second example data

CREATE TABLE #MyHead

(

Row_ID INT,

Promo_ID INT,

[Desc] VARCHAR(10)

)

--===== First Data Scenario

TRUNCATE TABLE #MyHead

INSERT INTO #MyHead

(Row_ID,Promo_ID,[Desc])

SELECT '1','1','desc1' UNION ALL

SELECT '2','1','desc2' UNION ALL

SELECT '3','2','desc3' UNION ALL

SELECT '4','3','desc4' UNION ALL

SELECT '5','4','desc5' UNION ALL

SELECT '6','1','desc6' UNION ALL

SELECT '7','2','desc7'

--== This should have an output 2,6,8

Same as to my previous post, just for tracing purposes...

#temp_dup --> temporary storage on where i will put those

Promo_ID's that I've encountered without

duplicate.

#temp_result --> List of Row_ID's for the final result

From row 1 to row 2, since I saw a duplicate..I would list the Row_ID on #temp_result of the last duplicate I encountered and that would be Row_ID 2 and #temp_dup will contain the last Promo_ID encountered with during duplicate. So my temp tables will now contain the following values.

#temp_dup (Promo_ID temp holder)

1

#temp_result (Row_ID range)

2

while continuing on rows 3,4,5, since I haven't found a duplicate...I will list the Promo_ID's on my #temp_dup.

#temp_dup (Promo_ID temp holder)

1,2,3,4

#temp_result (Row_ID range)

2

continuing on row 6 which has Promo_ID 1 and since I saw a duplicate comparing on my #temp_dup list, I will list the row_id of the last duplicate I encountered on #temp_result and erase the data on my #temp_dup and adding new data Promo_ID 1 (since this is the last data I encountered during duplicate). So my temp tables will now contain the following values.

#temp_dup (Promo_ID temp holder)

1

#temp_result (Row_ID range)

2,6

continuing on row 7, since there is no duplicate comparing to my #temp_dup so I will add the promo_id to #temp_dup and add (total rows + 1) on my #temp_result which is 8.

#temp_dup (Promo_ID temp holder)

1,2

#temp_result (Row_ID range)

2,6,8

[/code]

So the final result will be 2,6,8 as listed on #temp_result table. Which means that the range of ID's covered is from 1-2, 2-6, and 6-8.

Jeff/Steve,

I really apologize for the waste of time that has caused this post but I really find it hard to explain since this is really a big process as a whole and I could not just post the stored procedure here since it is confidential. What I posted here is just a chunk/part of the whole process logic which needs to have an output based from what I have posted for every scenario.

In addition to this, the script will accept dynamic table which means it can have multiple PK's. The objective is to list all the duplicate keys in an interleaving rows and return the range of row_id's to be processed.

What I forgot to post was for every scenario there is possibility that the data is on descending order or jumbled. So it can be like this:

--===== First Data Scenario

TRUNCATE TABLE #MyHead

INSERT INTO #MyHead

(Row_ID,Promo_ID,[Desc])

SELECT '1','5','desc1' UNION ALL

SELECT '2','4','desc2' UNION ALL

SELECT '3','3','desc3' UNION ALL

SELECT '4','2','desc4' UNION ALL

SELECT '5','1','desc5' UNION ALL

SELECT '6','1','desc6' UNION ALL

SELECT '7','2','desc7' UNION ALL

SELECT '8','3','desc8' UNION ALL

SELECT '9','1','desc9' UNION ALL

SELECT '10','2','desc10' UNION ALL

SELECT '11','3','desc11'

--== This should have an output 6,9,12

Thank you very much for your help.

sgmunson SSC Guru Points: 110640 More actions · Answer 13

As much as I hate to be the bearer of bad news, I really don't think there's any viable way for anyone to help you out here. The primary obstacle is that you have yet to disclose the EXACT and COMPLETE set of conditions under which a given record needs to be included in the output result set. There are many possible techniques for determining ranges of records to deal with, and if your objective is to determine a set of ranges, a tally table is often a very useful companion in doing so. However, since we don't yet know exactly what qualifies a record for inclusion, or how to determine what range of records to deal with, it's as if you want us to solve a problem for which we don't know the rules. In case you haven't figured it out yet, that's neither reasonable nor is it even possible for someone to come up with a solution that would be sure of working.

We understand about certain things being proprietary, but if you can use an accurate analogy, then we might have a chance to help. Lacking that, I can't see anyone spending any more time on this. If you want help, you've got to at least provide a complete set of details on exactly what the problem is. As you can see, the alternative that you keep presenting isn't working, and never could.

Steve

(aka smunson)

:ermm::ermm::ermm:

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sgmunson SSC Guru Points: 110640 More actions · Answer 14

One more attempt on my part:

DECLARE @MyHead TABLE

(

Row_ID INT,

Promo_ID INT,

[Desc] VARCHAR(10)

)

--===== First Data Scenario

INSERT INTO @MyHead (Row_ID,Promo_ID,[Desc])

SELECT '1','1','desc1' UNION ALL

SELECT '2','1','desc2' UNION ALL

SELECT '3','2','desc3' UNION ALL

SELECT '4','3','desc4' UNION ALL

SELECT '5','4','desc5' UNION ALL

SELECT '6','1','desc6' UNION ALL

SELECT '7','2','desc7'

--== This should have an output 2,6,8

/*

--===== Another Data Scenario

INSERT INTO @MyHead (Row_ID,Promo_ID,[Desc])

SELECT '1','5','desc1' UNION ALL

SELECT '2','4','desc2' UNION ALL

SELECT '3','3','desc3' UNION ALL

SELECT '4','2','desc4' UNION ALL

SELECT '5','1','desc5' UNION ALL

SELECT '6','1','desc6' UNION ALL

SELECT '7','2','desc7' UNION ALL

SELECT '8','3','desc8' UNION ALL

SELECT '9','1','desc9' UNION ALL

SELECT '10','2','desc10' UNION ALL

SELECT '11','3','desc11'

--== This should have an output 6,9,12

*/

DECLARE @MAX_ROW INT, @LAST_PID INT

SELECT @MAX_ROW = MAX(Row_ID) FROM @MyHead

SET @LAST_PID = (SELECT Promo_ID FROM @MyHead WHERE Row_ID = @MAX_ROW)

;WITH MYHEAD_PLUS AS (

SELECT Row_ID, Promo_ID

FROM @MyHead

WHERE Row_ID <> 1

UNION ALL

SELECT @MAX_ROW + 1, @LAST_PID

)

SELECT B.Row_ID

FROM @MyHead AS A INNER JOIN MYHEAD_PLUS AS B

ON A.Row_ID + 1 = B.Row_ID

WHERE B.Promo_ID = A.Promo_ID OR

ABS(B.Promo_ID - A.Promo_ID) > 1

Note that you'll have to test this with the other scenarios than these last two from your most recent post to see if it produces the correct result. You'll also have to figure out what I did on your own.

Steve

(aka smunson)

:ermm::ermm::ermm:

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)