Removing duplicates

Hi all,
I have a table like
UserNo UserName
1 Ajit
2 Ajit
3 Ajit
4 Vishal
5 Sonu
6 Sonu
7 Ketan etc

AndI want to remove all duplicates in this table any suggestions. Idea

[364 byte] By [Ajit_C#] at [2007-12-16]

«« Typde Untyped DataSet
»» The wrong Principal is passed to my remoted object

# 1

Here is one that would keep the first entry for each UserName.
delete tb
where UserNo not in(
select min(UserNo)
from tb
group by UserName)

oj at 2007-9-9 >

top of Msdn Tech,SQL Server,Transact-SQL...

# 2

Hi,

I assume UserNo is unique and you want to keep the first of each user. You can determine the id of the first occurence of each UserName by finding the first id using MIN, like this: SELECT MIN(UserNo) FROM tablename GROUP BY UserName

Now you selected the rows you do not want to be deleted, so you have to delete everything that is not in the resultset above, so that would look like this:
DELETE FROM tablename
WHERE UserNo NOT IN (SELECT MIN(UserNo) FROM tablename GROUP BY UserName)

Good luck!

BartCoppens at 2007-9-9 >

# 3

Thanks for the suggestion it works

Ajit_C#1 at 2007-9-9 >

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech