Selecting the top 5 unique rows, sorted randomly in SQL Server 2005?

Question

I have a table in SQL Server 2005 that looks like

1 | bob | joined
2 | bob | left
3 | john | joined
4 | steve | joined
5 | andy | joined
6 | kyle | joined

What I want is to give someone the ability to pull up the activity of 5 random users (showing their latest activity)

ex: I want to return results 1, 3, 4, 5, 6 - or - 2, 3, 4, 5, 6 - but never - 1, 2, 3, 4, 5 (because 1 and 2 are activities from the same user, and I don't want him showing up twice at the expense of a different unique user that could have their activity displayed)

I'm trying something like SELECT TOP(5) FROM table ORDER BY NEWID() to get the top 5 and the random aspect going, but when i try to incorporate UNIQUE or DISTINCT anywhere (to stop from receiving back both rows 1 and 2) i get SQL errors and i have no idea how to progress

Answer 1

select top 5 name, id from (
    select top 99.999 PERCENT name,id, NEWID() dummy from sysobjects
    order by dummy) dummyName

This works just replace the column names and tables for the ones you want

Answer 2

Using a CTE:

WITH cte AS (
   SELECT t.id,
          t.name,
          t.txt,
          ROW_NUMBER() OVER(PARTITION BY t.name
                                ORDER BY NEWID()) AS rank
     FROM TABLE t)
  SELECT TOP 5
         c.id, 
         c.name,
         c.txt
    FROM cte c
   WHERE c.rank = 1
ORDER BY NEWID()

Non-CTE equivalent:

  SELECT TOP 5
         c.id, 
         c.name,
         c.txt
    FROM (SELECT t.id,
                 t.name,
                 t.txt,
                 ROW_NUMBER() OVER(PARTITION BY t.name
                                       ORDER BY NEWID()) AS rank
            FROM TABLE t) c
   WHERE c.rank = 1
ORDER BY NEWID()

Answer 3

you can look at that question http://stackoverflow.com/questions/3633543/alternative-to-order-by-rand-on-joined-tables/3633555#3633555 for my answer.