Hi Good Please Help pleae in duplicate data

[whin_lopez]

Hi im new to sql server 2005 and i have a big problem.

i want to delete a record having the same name and surname

Here is my table named Students

StudID FirstName LastName
1 Erwin Lopez
2 Archie Lopez
3 Lobanz Lopez
4 Erwin Lopez
5 Chie Lopez
6 Erwin Lopez
7 Lobanz Lopez
8 Erwin Lopez

Here's the catch i want to delete entry that has same firstname and lastname
but assuming that i dont know what is the firstname i just want to delete duplicating
entry if ever a user entered a same firstname and lastname

i tried distinct but im having a hard time please help me guys =(
here is my sample:
DELETE FROM Students WHERE FirstName NOT IN
(SELECT DISTINCT FirstName FROM Students GROUP BY StudID, FirstName, LastName)
-- i thought that every entry that is not in the distinct will be deleted im kinda confuse please help

[BitBlt]

Common de-duping problem. Part of the issue is that you need to identify which records you want to KEEP, then delete everything else. To do that, you have to correlate your subselect so that the ID's of the records you want to keep are excluded from your outside select. One way to do this could look like:

select * 
from dbo.students a
where studid not in
(
select min(studId) 
from dbo.students b
where b.firstname = a.firstname 
and b.lastname = a.lastname
)

This snippet assumes you want to keep the first (lowest number?) ID in the table. You can of course select the last one (highest number?) by changing the min() to max().

Hope this helps!

[whin_lopez]

Hi Sir thanks for the reply, i tried your code and it gives me this result

StudID FirstName LastName
19 Archie Lopez
16 Erwin Lopez

it somehow worked but whin lopez should also count, what i mean is i want to delete the repeating name like delete all reapeating name only one erwin will retain but without using delete from students where firstname='erwin'

[urtrivedi]

I will suggest you unaurthodox method if student id is not important for you.

I assume that student id is autonumber

1) first notice the maximum student id in your table. Here I assume it is 19
2) now insert distinct name in same table from same table using following query

insert into student_table (firstname,lastname) select distinct firstname, lastname from student_table

3) now delete all student id which is having studid less than or equal to 19

delete from student_table where studid<=19

I have one question. Is it not possible that 2 or more student may have same fisrtname, lastname?

[BitBlt]

Please post the actual delete statement you used. It should look something like:

delete a
from dbo.students a
where studid not in
(
select min(studId) 
from dbo.students b
where b.firstname = a.firstname 
and b.lastname = a.lastname
)

If it doesn't look like that, then you misunderstood what I posted, or used it incorrectly.