How to select just one id from a set of repetitive records in SQL and select only one of the corresponding values

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)

Solution 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

Solution 2:

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