Author |
Topic |
massspectrometry
Yak Posting Veteran
50 Posts |
Posted - 2007-09-05 : 04:16:32
|
Hai there... I'm facing a problem when i want to delete.. I have a data that looks like this...SERIAL PIN100001 88100001 77100002 66100002 99900001 88900002 66So 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 PIN100001 77100002 99900001 88900002 66how am i to do this? |
|
Kristen
Test
22859 Posts |
Posted - 2007-09-05 : 04:20:45
|
See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Deleting+Duplicate+Records |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-05 : 04:21:01
|
E 12°55'05.25"N 56°04'39.16" |
|
|
Koji Matsumura
Posting Yak Master
141 Posts |
Posted - 2007-09-05 : 04:23:13
|
Which record do you want to keep?Lowest SERIAL? |
|
|
massspectrometry
Yak Posting Veteran
50 Posts |
Posted - 2007-09-05 : 04:28:13
|
I know.. i already try it.. this is my query.. DELETE SERIALTABLEFROM SERIALTABLEJOIN(select [PIN], max([SERIAL]) AS SerialIDfrom SERIALTABLEgroup by [PIN]) AS GON 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... |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-05 : 04:34:28
|
Oh, I think we have misinterpreted this request. It has nothing to with duplicates.See this solutionDECLARE @Sample TABLE (Serial INT, Pin INT)INSERT @SampleSELECT 100001, 88 UNION ALLSELECT 100001, 77 UNION ALLSELECT 100002, 66 UNION ALLSELECT 100002, 99 UNION ALLSELECT 900001, 88 UNION ALLSELECT 900002, 66select * from @sample order by pin, serialDELETE xFROM @Sample AS xINNER JOIN ( SELECT Pin, MAX(Serial) AS theSerial FROM @Sample GROUP BY Pin ) AS p ON p.Pin = x.Pin AND p.theSerial > x.Serialselect * from @sample order by pin, serial E 12°55'05.25"N 56°04'39.16" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-05 : 04:35:26
|
quote: Originally posted by massspectrometry I know.. i already try it.. this is my query.. DELETE SERIALTABLEFROM SERIALTABLEJOIN(select [PIN], max([SERIAL]) AS SerialIDfrom SERIALTABLEgroup by [PIN]) AS GON 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" |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-09-05 : 04:41:40
|
[code]DECLARE @SERIALTABLE TABLE( SERIAL int, PIN int)INSERT INTO @SERIALTABLESELECT 100001, 88 UNION ALLSELECT 100001, 77 UNION ALLSELECT 100002, 66 UNION ALLSELECT 100002, 99 UNION ALLSELECT 900001, 88 UNION ALLSELECT 900002, 66DELETE dFROM @SERIALTABLE d INNER JOIN ( SELECT PIN, SERIAL = MAX(SERIAL) FROM @SERIALTABLE GROUP BY PIN ) k ON d.PIN = k.PIN AND d.SERIAL <> k.SERIALSELECT *FROM @SERIALTABLE/*SERIAL PIN ----------- ----------- 100001 77 100002 99 900001 88 900002 66 */[/code] KH[spoiler]Time is always against us[/spoiler] |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-09-05 : 04:43:15
|
or DELETE dFROM @SERIALTABLE d left JOIN ( SELECT PIN, SERIAL = MAX(SERIAL) FROM @SERIALTABLE GROUP BY PIN ) k ON d.PIN = k.PIN AND d.SERIAL = k.SERIALWHERE k.PIN IS NULL KH[spoiler]Time is always against us[/spoiler] |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-09-05 : 04:44:37
|
D A M N ! ! ! where all that comes from ? KH[spoiler]Time is always against us[/spoiler] |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-05 : 04:45:52
|
For SQL Server 2005DELETE fFROM ( SELECT ROW_NUMBER() OVER (PARTITION BY Pin ORDER BY Serial DESC) AS RecID FROM @Sample ) AS fWHERE RecID > 1 E 12°55'05.25"N 56°04'39.16" |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-05 : 05:17:04
|
"Which record do you want to keep?Lowest SERIAL?"Note that the OPs example appears to show ramdon SERIAL keptKristen |
|
|
massspectrometry
Yak Posting Veteran
50 Posts |
Posted - 2007-09-05 : 05:31:54
|
erm...(pls don't be angry) may i know where to fing ROW_NUMBER()? Is it in script library? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-05 : 05:44:21
|
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" |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
johnsql
Posting Yak Master
161 Posts |
Posted - 2007-09-05 : 10:48:35
|
More work if data like these for table @SampleINSERT @SampleSELECT 100001, 88 UNION ALLSELECT 100001, 77 UNION ALLSELECT 100002, 66 UNION ALLSELECT 100002, 66 UNION ALLSELECT 900002, 66 UNION ALLSELECT 100002, 99 UNION ALLSELECT 900001, 88 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-05 : 10:57:11
|
Still work, because of "SELECT 900002, 66 UNION ALL". But if you delete this sample data, output isSerial Pin100002 66100002 66100001 77900001 88100002 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" |
|
|
massspectrometry
Yak Posting Veteran
50 Posts |
Posted - 2007-09-05 : 21:17:40
|
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.. ExampleSERIAL PIN100001 88 <-- duplicate pin start from here100001 77100002 66100002 99||(millions of data) <-- i don't want to delete anything from here..||900001 88 <-- starting from here to check the duplicate pin900002 66is it possible to add a condition like this? :? |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-09-06 : 02:57:27
|
If you have identity column (without any gap) , then you could useDelete from ...(select columns from table where indentity_col>1000000)...MadhivananFailing to plan is Planning to fail |
|
|
massspectrometry
Yak Posting Veteran
50 Posts |
Posted - 2007-09-06 : 04:03:56
|
how?.. is it a range? |
|
|
Next Page
|