CODEWITHSUNDEEP
sql-server
GullitsMullet
GullitsMullet

Reputation: 358

Select a random row from each group SQL Server

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?

Upvotes: 6

Views: 11228

Answers (4)

Ullas
Ullas

Reputation: 11556

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;

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28890

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

Upvotes: 16

Chanukya
Chanukya

Reputation: 5893

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

Upvotes: 2

Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

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

Upvotes: 2

Related Questions