Author |
Topic |
dwither_07
Starting Member
3 Posts |
Posted - 2008-08-11 : 04:37:52
|
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 dataCREATE 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)
17689 Posts |
Posted - 2008-08-11 : 04:56:43
|
can you explain how to get the desired output ? KH[spoiler]Time is always against us[/spoiler] |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-11 : 05:21:54
|
I think he means "sequential" keys.1) Keep record with smallest ROW_ID for each Promo_ID2) Report all other Row_ID's. E 12°55'05.25"N 56°04'39.16" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-11 : 05:28:33
|
[code]DECLARE @Sample TABLE ( RowID TINYINT, PromoID TINYINT )INSERT @SampleSELECT 1, 1 UNION ALLSELECT 2, 1 UNION ALLSELECT 3, 2 UNION ALLSELECT 4, 3 UNION ALLSELECT 5, 4 UNION ALLSELECT 6, 1 UNION ALLSELECT 7, 2SELECT 1 AS Scenario, RowIDFROM ( SELECT RowID, MIN(RowID) OVER (PARTITION BY PromoID) AS MinID FROM @Sample ) AS dWHERE RowID <> MinIDDELETEFROM @SampleINSERT @SampleSELECT 1, 1 UNION ALLSELECT 2, 2 UNION ALLSELECT 3, 3 UNION ALLSELECT 4, 4 UNION ALLSELECT 5, 5 UNION ALLSELECT 6, 1 UNION ALLSELECT 7, 2 UNION ALLSELECT 8, 3 UNION ALLSELECT 9, 4 UNION ALLSELECT 10, 5 UNION ALLSELECT 11, 1 UNION ALLSELECT 12, 2 UNION ALLSELECT 13, 3 UNION ALLSELECT 14, 4 UNION ALLSELECT 15, 5[/code] E 12°55'05.25"N 56°04'39.16" |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-11 : 05:39:10
|
[code]SELECT t.*FROm YourTable tLEFT JOIN (SELECT PromoID,MIN(Row_ID) AS mInRow FROm YourTable GROUP BY PromoID) tmpON tmp.MinRow=t.Row_IDAND tmp.PromoID=t.PromoIDWHERE tmp.MinRow IS NULL[/code] |
|
|
dwither_07
Starting Member
3 Posts |
Posted - 2008-08-12 : 02:27:50
|
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,10Just 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 resultLet 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)4continue 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)45continue 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) 26#temp_result (Row_ID range)45to 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)457On 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)4578On 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) 61#temp_result (Row_ID range)457810The 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. |
|
|
dwither_07
Starting Member
3 Posts |
Posted - 2008-08-12 : 02:31:00
|
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 |
|
|
|
|
|