Getting filtered results with subquery

Posted by josepv on Stack Overflow See other posts from Stack Overflow or by josepv
Published on 2010-06-15T18:55:25Z Indexed on 2010/06/15 19:02 UTC
Read the original article Hit count: 380

Filed under:
|
|

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

© Stack Overflow or respective owner

Related posts about sql

Related posts about server