1

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
Siyual
  • 16,415
  • 8
  • 44
  • 58
A.N.26
  • 11
  • 2
  • Possible duplicate of [Select n random rows from SQL Server table](http://stackoverflow.com/questions/848872/select-n-random-rows-from-sql-server-table) – Paul Williams Aug 24 '16 at 13:43
  • I dont need the top 10 percent, I need random records per group. – A.N.26 Aug 24 '16 at 13:45

2 Answers2

2

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.

Siyual
  • 16,415
  • 8
  • 44
  • 58
0

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
JonWay
  • 1,585
  • 17
  • 37