Delete duplicate rows from table expect one row

Posted by Kundan64 under Sql Server on 1/8/2013 | Points: 10 | Views : 1260 | Status : [Member] | Replies : 7
I have table CityInfo like:

City State
NewDelhi Delhi
NewDelhi Delhi
Gurgaon HR
Noida UP
Noida UP

I want the Query for delete the Duplicate City from the table, but one record must be available in table, after deletion table record should be like:

City State
NewDelhi Delhi
Gurgaon HR
Noida UP




Responses

Posted by: Amaren1982 on: 1/8/2013 [Member] Starter | Points: 25

Up
0
Down
create table CityMaste
(
ID int identity(1,1),
City varchar(200),
State varchar(200)
)


insert into CityMaste (city,State) values ('New Delhi','Delhi')
insert into CityMaste (city,State) values ('New Delhi','Delhi')
insert into CityMaste (city,State) values ('Gurgaon','HR')
insert into CityMaste (city,State) values ('Noida','UP')
insert into CityMaste (city,State) values ('Noida','UP')

1st method
delete from CityMaste where ID not in ( select max(ID) from CityMaste group by city)

2nd method
delete FROM CityMaste WHERE CityMaste.%%physloc%% NOT IN (SELECT MIN(cm.%%physloc%%) FROM CityMaste cm GROUP BY cm.city)

Warm Regards,
AMARENDRA KUMAR AMAR
SSE
9990662544
amaren1982@gmail.com

Kundan64, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Saratvaddilli on: 1/8/2013 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Do one thing

Add an identity column
Perform this action

DELETE

FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2)

Drop the identity column


--------OR-------------

Select the distinct records create a temp table , drop the original table and rename the temp table to original table


Thanks and Regards
V.SaratChand
Show difficulties that how difficult you are

Kundan64, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Raja_89 on: 1/9/2013 [Member] Starter | Points: 25

Up
0
Down
Hai
Thanks dude

Is it possible to get like std1000



Regards

Kundan64, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Saratvaddilli on: 1/9/2013 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi raja,
can you please make your question clear

Thanks and Regards
V.SaratChand
Show difficulties that how difficult you are

Kundan64, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Raja_89 on: 1/9/2013 [Member] Starter | Points: 25

Up
0
Down
Hai Dude
You can achieve this by %%lockres %%

DELETE
FROM dummytable
WHERE dummytable.%%lockres%%
NOT IN (SELECT MIN(b.%%lockres%%)
FROM dummytable b
GROUP BY b.city_info)






Kundan64, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Raja_89 on: 1/9/2013 [Member] Starter | Points: 25

Up
0
Down
Hai Dude

If i click the button i want to generate "std1000" to std10001 untill max value [ASP.NET C#]


Kundan64, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kundan64 on: 1/10/2013 [Member] Starter | Points: 25

Up
0
Down
Is this possible without use of Identity column?

Kundan64, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response