| Author |
 Topic  |
|
|
dwither_07
Starting Member
USA
3 Posts |
 Posted - 08/11/2008 : 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 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
16746 Posts |
Posted - 08/11/2008 : 04:56:43
|
can you explain how to get the desired output ?
KH
Time is always against us
|
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/11/2008 : 05:21:54
|
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" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/11/2008 : 05:28:33
|
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" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 08/11/2008 : 05:39:10
|
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 |
|
|
|
dwither_07
Starting Member
USA
3 Posts |
Posted - 08/12/2008 : 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,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. |
|
|
|
dwither_07
Starting Member
USA
3 Posts |
Posted - 08/12/2008 : 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 |
|
|
| |
Topic |
|
|
|
Get the Range ID's
