sql server - Getting filtered results with subquery -

I have something similar to the following:

  ID name color --- - ------- 1 Bob Blue 2 John Yellow 1 Bob Green 3 Sarah Red 3 Sarah Green  

What I would like to do, return a filtered list of results returned the following data Gone:

  ID name color ------------ 1 Bob Blue 2 John Yellow 3 All red  

i.e. I would like to return 1 line per user (I do not have any particular thing that special Which line has been returned for the AUS user - I need that [ID] is unique.) I already have some work but actually slow, where I created a floating table by adding all the IDs and then To select the top 1 from the same table, include the "external application" i.e.

  create table # btb ([id] [int]) #db select [id] from iTable T Select 1. [Id], t2 [Name], Apply T2.Color from #TB TO Outer (SELECT TOP 1 * from MyTable T2 WHERE T2. [ID] = T. [ID]) AS V2 Drop Table # TB  

Can someone suggest how I can improve it? Try

thanks

Try: as CTE (selection number ROW_NUMBER) ( Select the ID, name, color from CTE where the row name = 1

or < / P> Choose from *

  from (select ID, name, color, rank) (as the rank from the table group by the ID (ID) on the basis of the ID segment (ID)) HR rank where rank = 1