Aggregate randomly?

Question

I'm using Microsoft's SQL Server 2008. I need to aggregate by a foreign key to randomly get a single value, but I'm stumped. Consider the following table:

id          fk          val
----------- ----------- ----
1           100         abc
2           101         def
3           102         ghi
4           102         jkl

The desired result would be:

fk          val
----------- ----
100         abc
101         def
102         ghi

Where the val for fk 102 would randomly be either "ghi" or "jkl".

I tried using NEWID() to get unique random values, however, the JOIN fails since the NEWID() value is different depending on the sub query.

WITH withTable AS (
    SELECT id, fk, val, CAST(NEWID() AS CHAR(36)) random
    FROM exampleTable
)
SELECT t1.fk, t1.val
FROM withTable t1
JOIN (
    SELECT fk, MAX(random) random
    FROM withTable
    GROUP BY fk
) t2 ON t2.random = t1.random
;

I'm stumped. Any ideas would be greatly appreciated.

Answer 1

I might think about it a little differently, using a special ranking function called ROW_NUMBER().

You basically apply a number to each row, grouped by fk, starting with 1, ordered randomly by using the NEWID() function as a sort value. From this you can select all rows where the row number was 1. The effect of this technique is that it randomizes which row gets assigned the value 1.

WITH withTable(id, fk, val, rownum) AS 
(
    SELECT
        id, fk, val, ROW_NUMBER() OVER (PARTITION BY fk ORDER BY NEWID())
    FROM
        exampleTable
)
SELECT
    *
FROM
    withTable
WHERE
    rownum = 1

This approach has the added benefit in that it takes care of the grouping and the randomization in one pass.

Answer 2

You can do this not with aggregation but with row_number():

select id, fk, val
from (select t1.*,
             row_number() over (partition by fk order by newid()) as seqnum
      from withTable t1
     ) t1
where seqnum = 1