| Author |
 Topic  |
|
|
lols
Posting Yak Master
174 Posts |
 Posted - 04/21/2008 : 02:27:29
|
I have the data as below:
DECLARE @Call TABLE(CallID INT, CallType INT, CallDetailID INT)
INSERT @Call
SELECT 111, 1, 12123 UNION ALL
SELECT 112, 1, 12124 UNION ALL
SELECT 114, 2, 12125 UNION ALL
SELECT 123, 2, 12124 UNION ALL
SELECT 134, 1, 12123 UNION ALL
SELECT 143, 1, 65322 UNION ALL
SELECT 145, 1, 65326 UNION ALL
SELECT 154, 2, 65323 UNION ALL
SELECT 185, 1, 65322 UNION ALL
SELECT 195, 3, 65323
Here an item is repeated if the calltype and detail are the same. SO if you see, the ID 134 and 185 are repeatin.
I want to do two things. First find the repeated items and list it using a select query and then second, remove the repeated items from the table.
|
|
|
RyanRandall
Flowing Fount of Yak Knowledge
United Kingdom
1074 Posts |
Posted - 04/21/2008 : 05:09:21
|
select * from @Call a where exists
(select * from @Call where CallType = a.CallType and CallDetailID = a.CallDetailID and CallID < a.CallID)
delete a from @Call a where exists
(select * from @Call where CallType = a.CallType and CallDetailID = a.CallDetailID and CallID < a.CallID)
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part. |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/21/2008 : 05:22:02
|
1)
SELECT CallType,
CallDetailID
FROM @Call
GROUP BY CallType,
CallDetailID
HAVING COUNT(*) > 1
2)
DELETE c
FROM @Call AS c
LEFT JOIN (
SELECT MIN(CallID) AS CallID
FROM @Call
GROUP BY CallType,
CallDetailID
HAVING COUNT(*) > 1
) AS x ON x.CallID = c.CallID
WHERE x.CallID IS NULL
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
lols
Posting Yak Master
174 Posts |
Posted - 04/21/2008 : 23:01:40
|
| thanks a lot |
|
|
| |
Topic |
|
|
|
Repeating items
