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 

Answer 3

with cte as
(
select *,rank() over ( partition  by id order by Newid()) as rn from #c
)
select id,code,age from cte where rn=1

Answer 4

To select different sets each time, use checksum(newid()) in the order by clause.

Query

;with cte as(
    select *, rn = row_number() over(
        partition by ID
        order by abs(checksum(newid())) % 15
    )
    from [your_table_name]
)
select * from cte
where rn = 1;