Click here to Skip to main content
15,936,758 members
Please Sign up or sign in to vote.
4.50/5 (2 votes)
I have a table like this.

Id Name
1 Samy
1 Sammy
1 Sam
2 John
3 Anne

I wan't to take distinct Id's and relevant names for the ID. But for the Id 1 has 3 different names. In any case like this I can take any one name out of those 3 names. How can I write a Select sql statment for this case. I'm using Ms Sql server.

Try this
select  [Id],
        [Name] = (select top 1 [Name] from MyTable where [Id] = a.[Id] order by [Name])
from    (
        select  distinct
        from    MyTable
        ) a
Share this answer
Albin Abel 15-Apr-11 8:08am    
This is fine. My 5
Sanyon_d 17-Apr-11 23:19pm    
Thanks Costica
Sanyon_d 18-Apr-11 12:15pm    
This sql cant use in SqlServer2000. How can I use this in sqlserver2000
As I am getting you question you want to get only one Name out of many Name for specific ID if so then you can try the given syntax
select Top 1 ID,Name from TableName where ID='Searching-data' order by NewID()

when you use given syntax for your requirement as given below
select Top 1 ID,Name from TableName where ID='1' order by NewID()

you will get output like
ID Name
1 any_one_out_of(Samy,Sammy & Sam)
Share this answer
Sanyon_d 17-Apr-11 23:21pm    
Thanks Ravi

This would be a poor design of the table. However as you got data already you may try this

SELECT id,name FROM (SELECT id, name,RANK() OVER
    (PARTITION BY id ORDER BY name) AS Rank
FROM yourTableName  ) as  RankedTable WHERE Rank=1
Share this answer
Sanyon_d 17-Apr-11 23:19pm    
Thanks Albin

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900