SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Get the Range ID's
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dwither_07
Starting Member

USA
3 Posts

Posted - 08/11/2008 :  04:37:52  Show Profile  Reply with Quote
Hello All,

I'm new to ms sql, can someone tell me on how to get the following results (range of row_id's) based from the sample data given below (results are stated right after every data sample as comment).


--===== 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 idea here is to check the promo id with duplicate keys. Any good ideas or sample code is highly appreciated.

Thanks so much for your time.

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 08/11/2008 :  04:56:43  Show Profile  Reply with Quote
can you explain how to get the desired output ?


KH
Time is always against us

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/11/2008 :  05:21:54  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I think he means "sequential" keys.

1) Keep record with smallest ROW_ID for each Promo_ID
2) Report all other Row_ID's.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/11/2008 :  05:28:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
DECLARE	@Sample TABLE
	(
		RowID TINYINT,
		PromoID TINYINT
	)

INSERT	@Sample
SELECT	1, 1 UNION ALL
SELECT	2, 1 UNION ALL
SELECT	3, 2 UNION ALL
SELECT	4, 3 UNION ALL
SELECT	5, 4 UNION ALL
SELECT	6, 1 UNION ALL
SELECT	7, 2

SELECT	1 AS Scenario,
	RowID
FROM	(
		SELECT	RowID,
			MIN(RowID) OVER (PARTITION BY PromoID) AS MinID
		FROM	@Sample
	) AS d
WHERE	RowID <> MinID

DELETE
FROM	@Sample

INSERT	@Sample
SELECT	1, 1 UNION ALL
SELECT	2, 2 UNION ALL
SELECT	3, 3 UNION ALL
SELECT	4, 4 UNION ALL
SELECT	5, 5 UNION ALL
SELECT	6, 1 UNION ALL
SELECT	7, 2 UNION ALL
SELECT	8, 3 UNION ALL
SELECT	9, 4 UNION ALL
SELECT	10, 5 UNION ALL
SELECT	11, 1 UNION ALL
SELECT	12, 2 UNION ALL
SELECT	13, 3 UNION ALL
SELECT	14, 4 UNION ALL
SELECT	15, 5



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/11/2008 :  05:39:10  Show Profile  Reply with Quote
SELECT t.*
FROm YourTable t
LEFT JOIN (SELECT PromoID,MIN(Row_ID) AS mInRow
           FROm YourTable
           GROUP BY PromoID) tmp
ON tmp.MinRow=t.Row_ID
AND tmp.PromoID=t.PromoID
WHERE tmp.MinRow IS NULL
Go to Top of Page

dwither_07
Starting Member

USA
3 Posts

Posted - 08/12/2008 :  02:27:50  Show Profile  Reply with Quote
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.
Go to Top of Page

dwither_07
Starting Member

USA
3 Posts

Posted - 08/12/2008 :  02:31:00  Show Profile  Reply with Quote
Just an additional input, data can be on any order, like for example i can have a data like this one:

--===== 7th 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','4','desc9' UNION ALL
SELECT '10','5','desc10' UNION ALL
SELECT '11','6','desc11' UNION ALL
SELECT '12','5','desc12' UNION ALL
SELECT '13','4','desc13' UNION ALL
SELECT '14','3','desc14' UNION ALL
SELECT '15','2','desc15' UNION ALL
SELECT '16','1','desc16'

--== This should output 6,12,17
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000