How to "pick" random records with T-SQL

Question

This is a simple question that is actually hard to answer, because the "picking" has a special meaning.

I need to give three random picks for each person (and give pick/row number of 1, 2, and 3). What makes it hard is that the persons and picks are from different tables and there is no logical joining between the person and picks.

The closest I can get is:

SELECT TOP 15 database_id, create_date, RowNo, cs.name FROM sys.databases
CROSS apply ( 
  SELECT top 3 Row_number()OVER(ORDER BY (SELECT NULL)) AS RowNo,*
  FROM (SELECT top 3 name from sys.all_views ORDER BY NEWID()) T
  ) cs

I know the above is not person and picks, but it a working SQL that anyone can test it out without creating person and picks tables first. And,

It illustrates the problem I'm facing --

the above SQL will give each person the same picks, whereas I need to give different person different picks.

How to do that? Thx.

Answer 1

Adding a correlated condition inside the CROSS APPLY will solve your problem

SELECT TOP 15 database_id,
              create_date,
              RowNo,
              cs.NAME
FROM   sys.databases d
       CROSS apply (SELECT TOP 3 Row_number() OVER(ORDER BY (SELECT NULL)) AS RowNo, *
                    FROM   (SELECT TOP 3 NAME
                            FROM   sys.all_views v
                            WHERE  d.NAME = d.NAME --Here
                            ORDER  BY Newid()) T) cs 

Check the alias name in Where clause both LHS and RHS are from same table and same column it is just to execute the sub-query for each row in databases table