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.
| Author |
Topic |
|
imranabdulaziz
Yak Posting Veteran
83 Posts |
 Posted - 2008-03-26 : 07:35:38
|
| Hi ,i am using sql server 2005.i have one table where i need to find records that have same citycode and hospitalcode and doctorcode then delete the record keeping only one record of them my problem is table structure have idendtity column which is unique.that is m table structure is something like recid citycode hospcode doctorcode otherdesp1 0001 hp001 d0001 ...2 0002 hp002 d0002 ...3 0001 hp001 d0001 ...4 0002 hp002 d0002 ... please suggestthank you |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-26 : 07:37:23
|
DELETE fFROM (SELECT ROW_NUMBER() OVER (PARTITION BY CityCode, HospitalCode ORDER BY RecID DESC) AS xyz FROM Table1) AS fWHERE xyz > 1
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
imranabdulaziz
Yak Posting Veteran
83 Posts |
Posted - 2008-03-26 : 09:21:15
|
| Thank you.Wonderful approachThank you once again |
|
|
|
|
|
|
deleting the duplicate record from table