Delete same records from table but finally remains

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

General SQL Server Forums

New to SQL Server Programming

Delete same records from table but finally remains

Author

Topic

ashu1234
Starting Member

9 Posts

Posted - 2009-04-11 : 13:15:00

Hello All !!!!!!!!!

I want to know one thing "Delete same records from table but finally remains one "

i am using sql server 2005, i have one table "emprecords"
in this table their is same types of records like

id NAME USERID PASSORD ADDRESS PHONE
1 ASHU ashwani 123 New Delhi 9211556623
2 ASHU sdasd 231 new delhi 632332
3 ASHU fsfd 345 Pakistan 982311
4 KAMAL sdfsdf 644 tilak nagar 981111
5 ROHIT dgsf 345 oskfsl 9211

i want to do, i have to delete same TYPES of "NAME" like ASHU
all records of ASHU is delete remaining one name "ASHU"

NEW RECORDS IS
1 ASHU ashwani 123 New Delhi 9211556623
4 KAMAL sdfsdf 644 tilak nagar 981111
5 ROHIT dgsf 345 oskfsl 9211

Help me

ashwani

ashwani

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-11 : 13:32:15

Which record do you want to keep? Does it depend on something? Or do you want to keep any one ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-12 : 13:38:24

[code]
DELETE t
FROM Table t
LEFT JOIN (SELECT MAX(ID) AS MaxID,NAME
FROM Table
GROUP BY NAME) t1
ON t1.NAME=t.NAME
AND t1.MaxID=t.ID
WHERE t1.MaxID IS NULL
[/code]

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-04-12 : 23:44:32

Hi, Try this once

delete t from
( select row_number() over ( partition by name order by id ) as rn from urtable ) t
where t.rn > 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-15 : 13:35:59

quote:
Originally posted by Nageswar9

Hi, Try this once

delete t from
( select row_number() over ( partition by name order by id ) as rn from urtable ) t
where t.rn > 1

will work only from sql 2005 onwards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 13:48:38

Maybe that's why OP wrote "i am using sql server 2005"?