Repeating items - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Repeating items

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2008-04-21 : 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
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-21 : 05:09:21

[code]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)[/code]

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-21 : 05:22:02

[code]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[/code]

E 12°55'05.25"
N 56°04'39.16"

lols
Posting Yak Master

174 Posts

Posted - 2008-04-21 : 23:01:40

thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources