Pulling random records for each pair of values an another table SQL-CodePudding

I'm looking to pull 2 random student names in table 1 for each Class-Teacher pair in Table 2. I looked into using

Table 1

Class	Teacher
Math	Mr. Smith
Math	Mrs. Kim
Science	Mr. Smith
Science	Mrs. Kim

Table 2

Student
Matt
Jess
Tom
Sally
Rod
Nikki

Final Example

Class	Teacher	Student
Math	Mr. Smith	Matt
Math	Mr. Smith	Sally
Math	Mrs. Kim	Nikki
Math	Mrs. Kim	Rod
Science	Mr. Smith	Matt
Science	Mr. Smith	Jess
Science	Mrs. Kim	Tom
Science	Mrs. Kim	Rod

CodePudding user response：

You can use RANDOM() to sort the rows randombly and then pick the first two.

For example:

select
  a.*, x.*
from table1 a,
lateral (
  select  *
  from table2
  order by length(a.class) * 0   random()
  limit 2
) x

Result:

 class    teacher    student 
 -------- ---------- ------- 
 Math     Mr. Smith  Matt    
 Math     Mr. Smith  Jess    
 Math     Mrs. Kim   Nikki   
 Math     Mrs. Kim   Rod     
 Science  Mr. Smith  Rod     
 Science  Mr. Smith  Jess    
 Science  Mrs. Kim   Tom     
 Science  Mrs. Kim   Jess

See running example at DB Fiddle. Please note the results change every time you run the query.