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
 SQL Server Development (2000)
 Delete problem
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 09/05/2007 :  04:16:32  Show Profile  Send massspectrometry a Yahoo! Message  Reply with Quote
Hai there... I'm facing a problem when i want to delete.. I have a data that looks like this...

SERIAL PIN
100001 88
100001 77
100002 66
100002 99
900001 88
900002 66

So i want to delete the row of data based on the duplicate pin.. not the duplicate serial number.. So the result will look like this..

SERIAL PIN
100001 77
100002 99
900001 88
900002 66

how am i to do this?

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/05/2007 :  04:20:45  Show Profile  Reply with Quote
See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Deleting+Duplicate+Records
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30098 Posts

Posted - 09/05/2007 :  04:20:51  Show Profile  Visit SwePeso's Homepage  Reply with Quote
There are many examples found in the FAQ
found here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30098 Posts

Posted - 09/05/2007 :  04:21:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote




E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 09/05/2007 :  04:23:13  Show Profile  Reply with Quote
Which record do you want to keep?
Lowest SERIAL?
Go to Top of Page

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 09/05/2007 :  04:28:13  Show Profile  Send massspectrometry a Yahoo! Message  Reply with Quote
I know.. i already try it.. this is my query..

DELETE SERIALTABLE
FROM SERIALTABLE
JOIN
(select [PIN], max([SERIAL]) AS SerialID
from SERIALTABLE
group by [PIN]) AS G
ON G.[PIN] = shlserialkeys.[PIN]
WHERE SERIALTABLE.[SERIAL] < G.[SerialID]
AND SERIALTABLE.[PIN] = G.[PIN]

but this doesn't work.. :( It only delete a few data...
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30098 Posts

Posted - 09/05/2007 :  04:34:28  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Oh, I think we have misinterpreted this request. It has nothing to with duplicates.
See this solution
DECLARE	@Sample TABLE (Serial INT, Pin INT)

INSERT	@Sample
SELECT	100001, 88 UNION ALL
SELECT	100001, 77 UNION ALL
SELECT	100002, 66 UNION ALL
SELECT	100002, 99 UNION ALL
SELECT	900001, 88 UNION ALL
SELECT	900002, 66

select * from @sample order by pin, serial

DELETE		x
FROM		@Sample AS x
INNER JOIN	(
			SELECT		Pin,
					MAX(Serial) AS theSerial
			FROM		@Sample
			GROUP BY	Pin
		) AS p ON p.Pin = x.Pin AND p.theSerial > x.Serial

select * from @sample order by pin, serial




E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30098 Posts

Posted - 09/05/2007 :  04:35:26  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by massspectrometry

I know.. i already try it.. this is my query..

DELETE SERIALTABLE
FROM SERIALTABLE
JOIN
(select [PIN], max([SERIAL]) AS SerialID
from SERIALTABLE
group by [PIN]) AS G
ON G.[PIN] = shlserialkeys.[PIN]
WHERE ON SERIALTABLE.[SERIAL] < G.[SerialID]
AND SERIALTABLE.[PIN] = G.[PIN]


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

Edited by - SwePeso on 09/05/2007 04:36:17
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 09/05/2007 :  04:41:40  Show Profile  Reply with Quote
DECLARE @SERIALTABLE TABLE
(
	SERIAL	int,
	PIN	int
)

INSERT INTO @SERIALTABLE
SELECT	100001, 88 UNION ALL
SELECT	100001, 77 UNION ALL
SELECT	100002, 66 UNION ALL
SELECT	100002, 99 UNION ALL
SELECT	900001, 88 UNION ALL
SELECT	900002, 66

DELETE	d
FROM	@SERIALTABLE d
	INNER JOIN
	(
		SELECT	PIN, SERIAL = MAX(SERIAL)
		FROM	@SERIALTABLE
		GROUP BY PIN
	) k	
	ON	d.PIN		= k.PIN
	AND	d.SERIAL	<> k.SERIAL

SELECT	*
FROM	@SERIALTABLE

/*
SERIAL      PIN         
----------- ----------- 
     100001          77 
     100002          99 
     900001          88 
     900002          66 
*/



KH
Time is always against us

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 09/05/2007 :  04:43:15  Show Profile  Reply with Quote
or
DELETE	d
FROM	@SERIALTABLE d
	left JOIN
	(
		SELECT	PIN, SERIAL = MAX(SERIAL)
		FROM	@SERIALTABLE
		GROUP BY PIN
	) k	
	ON	d.PIN		= k.PIN
	AND	d.SERIAL	= k.SERIAL
WHERE	k.PIN	IS NULL



KH
Time is always against us

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 09/05/2007 :  04:44:37  Show Profile  Reply with Quote
D A M N ! ! !
where all that comes from ?


KH
Time is always against us

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30098 Posts

Posted - 09/05/2007 :  04:45:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
For SQL Server 2005
DELETE	f
FROM	(
		SELECT	ROW_NUMBER() OVER (PARTITION BY Pin ORDER BY Serial DESC) AS RecID
		FROM	@Sample
	) AS f
WHERE	RecID > 1



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/05/2007 :  05:17:04  Show Profile  Reply with Quote
"Which record do you want to keep?
Lowest SERIAL?
"

Note that the OPs example appears to show ramdon SERIAL kept

Kristen
Go to Top of Page

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 09/05/2007 :  05:31:54  Show Profile  Send massspectrometry a Yahoo! Message  Reply with Quote
erm...(pls don't be angry) may i know where to fing ROW_NUMBER()? Is it in script library?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30098 Posts

Posted - 09/05/2007 :  05:44:21  Show Profile  Visit SwePeso's Homepage  Reply with Quote
No, it is for SQL Server 2005, as I mentioned.
If you are running on SQL Server 2000, you can't use it.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22740 Posts

Posted - 09/05/2007 :  06:40:36  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Also Refer Nigel's more methods
http://sqlteam.com/forums/topic.asp?TOPIC_ID=6256

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

johnsql
Posting Yak Master

USA
161 Posts

Posted - 09/05/2007 :  10:48:35  Show Profile  Reply with Quote
More work if data like these for table @Sample
INSERT @Sample
SELECT 100001, 88 UNION ALL
SELECT 100001, 77 UNION ALL
SELECT 100002, 66 UNION ALL
SELECT 100002, 66 UNION ALL
SELECT 900002, 66 UNION ALL
SELECT 100002, 99 UNION ALL
SELECT 900001, 88
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30098 Posts

Posted - 09/05/2007 :  10:57:11  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Still work, because of "SELECT 900002, 66 UNION ALL".
But if you delete this sample data, output is
Serial	Pin
100002	66
100002	66
100001	77
900001	88
100002	99
and NOW you have the cases with duplicates! See link posted by Kristen.

PS: If using SQL Server 2005, my suggestion above would have taken care of this too.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 09/05/2007 :  21:17:40  Show Profile  Send massspectrometry a Yahoo! Message  Reply with Quote
Erm.. can i add a condition?.. like.. in one table there are millions of data.. but i only want to delete 1000 data with duplicate pin... and the duplicate pin is at the bottom.. Example

SERIAL PIN
100001 88 <-- duplicate pin start from here
100001 77
100002 66
100002 99
|
|
(millions of data) <-- i don't want to delete anything from here..
|
|
900001 88 <-- starting from here to check the duplicate pin
900002 66

is it possible to add a condition like this? :?

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22740 Posts

Posted - 09/06/2007 :  02:57:27  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
If you have identity column (without any gap) , then you could use

Delete from ...
(
select columns from table where indentity_col>1000000)
...


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 09/06/2007 :  04:03:56  Show Profile  Send massspectrometry a Yahoo! Message  Reply with Quote
how?.. is it a range?
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 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.12 seconds. Powered By: Snitz Forums 2000