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 2000 Forums
 Transact-SQL (2000)
 Repeating items
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lols
Posting Yak Master

174 Posts

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

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 04/21/2008 :  05:22:02  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

lols
Posting Yak Master

174 Posts

Posted - 04/21/2008 :  23:01:40  Show Profile  Reply with Quote
thanks a lot
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.06 seconds. Powered By: Snitz Forums 2000