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?
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())
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
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
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;
