Select a random row from each group SQL Server

Question

I have a table like below

ID   Code   Age
----------------
1    4758   21
1    7842   14
1    9821   23
1    6842   9
2    8472   24
2    7558   31
2    7841   28
3    7881   38
3    8794   42
3    4871   43    

For each ID, I want to select one of the rows at random like so

ID   Code   Age
----------------
1    7842   14    
2    7841   28
3    4871   43 

Is this possible in SQL Server?

Answer 1

select top 1 with ties id,code,age 
from
table
order by row_number() over (partition by id order by rand())

Update: as per this Return rows in random order, you have to use NEWId,since RAND() is fixed for the duration of the SELECT on MS SQL Server.

 select top 1 with ties id,code,age 
 from
 table
order by row_number() over (partition by id order by NEWID())

Answer 2

Use Newid() in order by clause of Row_number()

SELECT [ID], [Code], [Age]
FROM   (SELECT *,
               Row_number()
                 OVER(
                   PARTITION BY ID
                   ORDER BY Newid()) RNO
        FROM   #Table1)A
WHERE  RNO = 1