CODEWITHSUNDEEP
sqlsql-server-2008random
Lars Markussen
Lars Markussen

Reputation: 63

SQL: how to get random number of rows from one table for each row in another

I have two tables where the data is not related For each row in table A i want e.g. 3 random rows in table B

This is fairly easy using a cursor, but it is awfully slow

So how can i express this in single statement to avoid RBAR ?

Upvotes: 4

Views: 1370

Answers (4)

JensB
JensB

Reputation: 6850

Heres an example of how this could be done, code is self contained, copy and press F5 ;)

-- create two tables we can join 
DECLARE @datatable TABLE(ID INT)
DECLARE @randomtable TABLE(ID INT)

-- add some dummy data
DECLARE @i INT = 1
WHILE(@i < 3) BEGIN
    INSERT INTO @datatable (ID) VALUES (@i)
    SET @i = @i + 1
END 

SET @i = 1
WHILE(@i < 100) BEGIN
    INSERT INTO @randomtable (ID) VALUES (@i)
    SET @i = @i + 1
END 

--The key here being the ORDER BY newid() which makes sure that 
--the TOP 3 is different every time
SELECT 
    d.ID AS DataID
    ,rtable.ID RandomRow
FROM @datatable d
LEFT JOIN (SELECT TOP 3 * FROM @randomtable ORDER BY newid()) as rtable ON 1 = 1

Heres an example of the output

randomrow

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

To get a random number between 0 and (N-1), you can use.

abs(checksum(newid())) % N

Which means to get positive values 1-N, you use

1 + abs(checksum(newid())) % N

Note: RAND() doesn't work - it is evaluated once per query batch and you get stuck with the same value for all rows of tableA.

The query:

SELECT *
  FROM tableA A
  JOIN (select *, rn=row_number() over (order by newid())
          from tableB) B ON B.rn <= 1 + abs(checksum(newid())) % 9

(assuming you wanted up to 9 random rows of B per A)

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269933

If you have a fixed number that you know in advance (such as 3), then:

select a.*, b.*
from a cross join
     (select top 3 * from b) b

If you want a random number of rows from "b" for each row in "a", the problem is a bit harder in SQL Server.

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146499

assuming tableB has integer surrogate key, try

 Declare @maxRecs integer = 11 -- Maximum number of b records per a record
 Select a.*, b.*
 From tableA a Join tableB b
     On b.PKColumn % (floor(Rand() * @maxRecs)) = 0

Upvotes: 0

Related Questions