Getting filtered results with subquery

Question

I have a table with something like the following:

ID Name Color  
------------
1 Bob Blue  
2 John Yellow  
1 Bob Green  
3 Sara Red  
3 Sara Green

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

ID Name Color  
------------
1 Bob Blue  
2 John Yellow  
3 Sara Red  

i.e. I would like to return 1 row per user. (I do not mind which row is returned for the particular user - I just need that the [ID] is unique.) I have something already that works but is really slow where I create a temp table adding all the ID's and then using a "OUTER APPLY" selecting the top 1 from the same table, i.e.

CREATE TABLE #tb  
(  
    [ID] [int]  
)  

INSERT INTO #tb  
select distinct [ID] from MyTable

select 
    T1.[ID],
    T2.[Name],
    T2.Color  
from  
    #tb T1
    OUTER APPLY 
    (
        SELECT TOP 1 * FROM MyTable T2 WHERE T2.[ID] = T1.[ID]
    ) AS V2


DROP TABLE #tb

Can somebody suggest how I may improve it?

Thanks

Answer 1

If you're using SQL Server 2005 or higher, you could use the Ranking functions and just grab the first one in the list.

http://msdn.microsoft.com/en-us/library/ms189798.aspx

Answer 2

Try:

WITH CTE AS 
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS 'RowNo', 
ID, Name, Color
FROM table
)
SELECT ID,Name,color
FROM CTE
WHERE RowNo = 1

or

select 
  *
from
(
    Select 
      ID, Name, Color,
      rank() over (partition by Id order by sum(Name) desc) as Rank
    from 
      table
    group by 
      ID
) 
  HRRanks
where
  rank = 1