Sql-server – Distinct Random select

randomsql server

Hi can anyone help with this little MS SQL Puzzle.

I basically need to get a 1 random answer from each of the submitters.

id,   submitter,   answer

1,    James,        Blue

2,    James,        Red

3,    James,        Orange

4,    Ian,        Yellow

5,    Ian,        Pink

6,    Robert,       Red

I have tried the following but obviously this will always get the min(id) or Max(id) what I need is a random(id)?

select * from #tmpData 
 where id in (
  select min(id ) FROM #tmpData 
   group by submitter
            )

Thanks in advance..

Best Answer

Assuming a not very old version of SQL-Server, you can use ROW_NUMBER() to find one row per submitter:

; with r as
  ( select id, submitter, answer, 
           rnd = row_number() over (partition by submitter order by newid())
    from #tmpData 
  )
select id, submitter, answer
from r
where rnd = 1 ;

Test at: SQLfiddle

Related Question