CODEWITHSUNDEEP
sqlsql-server
A.N.26
A.N.26

Reputation: 11

Pull 'n' records from each group randomly in sql server

I need to pull 'n' number of records from each group randomly from a table. I have tried using rownum() over(partition by.....) but I not able to pull out per group. distinct on id wouldn't work because I need more than one record per group. consider the following table:

ST ID
MA  1
CA  2
IL  3
ME  4
MA  5
MA  6
MA  7
IL  8
ME  9
CA  10
CA  11
CA  12
ME  13
ME  14
IL  15
IL  16
IL  17

My o/p should look something like this: when n=2

MA  1
CA  2
IL  3
ME  4
MA  5
CA  10
IL  16
ME  13

Upvotes: 1

Views: 458

Answers (2)

JonWay
JonWay

Reputation: 1735

This should work. If you need more than one number you can UNION ALL to combine the data. Each UNION will give you random number

create table ##Rand1 (ST Varchar(10),ID INT)
-----=========================
INSERT INTO ##Rand1
Values
('MA',1),('CA',2),('IL',3),('ME',4),('MA',5),('MA',6),('MA',7),('IL',8),('ME',9),('CA',10),
('CA',11),('CA',12),('ME',13),('ME',14),('IL',15),('IL',16),('IL',17)

------=========================
DECLARE @Random INT
DECLARE @Upper INT
DECLARE @Lower INT
SET @Lower = (select min(ID) FROM ##Rand1) ---- The lowest random number
SET @Upper = (select MAX(ID) FROM ##Rand1)---- One more than the highest random number
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random AS ID

Upvotes: 0

Siyual
Siyual

Reputation: 16917

You can do this by ordering the Row_Number() by NewId() and pulling only the top N per group:

Declare @N Int = 2

;With Cte As
(
    Select  ST, ID, Row_Number() Over (Partition By ST Order By NewId()) RN
    From    YourTable
)
Select  ST, ID
From    Cte
Where   RN <= @N
Order By ID

Ordering by NewId() will randomly assign the Row_Number() value amongst the groups with each run. So, you only need to take those with a RN value <= your N value.

Upvotes: 2

Related Questions