1

So I have this table called students_classrooms which represents the relationship between students and classrooms. I have been wondering if there is a way I could get for each classroom one randomly assign student only with SQL query.

Looking for result like this: classroom - student (2,3)(3,77)

students are selected ranmdomly.

I have been trying different configurations solutions, but non of them seems to be working, would appreciate any suggestions, thanks!!

SELECT student_id, classroom_id
from students_classrooms
where classroom_id in (2,3)

students_classrooms

mrTarragona
  • 77
  • 7
  • 1
    Does this answer your question? [MySQL select 10 random rows from 600K rows fast](https://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast) – Serg Jan 14 '22 at 19:59
  • no, what I need is to get one random student from the students assigned to each classroom. So xe from classroom 2 just one student chosen randomly, same for classroom 3. – mrTarragona Jan 14 '22 at 20:07

2 Answers2

1

I think you need something like:

select ( 
         select t2.student_id 
         from students_classrooms t2 
         where t2.classroom_id = t1.classroom_id 
     order by rand() limit 1 
    ) as student_id,
          t1.classroom_id
from students_classrooms t1
group by classroom_id
order by rand() ;

The subquery randomly searches the same table, referencing the classroom_id, generating a random list

Demo

Ergest Basha
  • 7,870
  • 4
  • 8
  • 28
1

Here's an idea:

SELECT classroom_id,
       SUBSTRING_INDEX(GROUP_CONCAT(student_id ORDER BY RAND()),',',1) AS student
FROM students_classrooms 
WHERE classroom_id IN (2,3)
GROUP BY classroom_id;

Use GROUP_CONCAT(student_id ORDER BY RAND()) then extract the first student_id appeared in the GROUP_CONCAT() list using SUBSTRING_INDEX().

Fiddle

FanoFN
  • 6,815
  • 2
  • 13
  • 33