jeudi 2 avril 2020

MySQL randomized list with sequence in list

I have a table of students (id, name, project). I'd like a ramdomized sequence of students whose projects are '%science%'.

SELECT NAME, id FROM students WHERE project LIKE '%science%' ORDER BY RAND()

Here's the complication: I'd like to get each student's "sequence number" in the randomized list. This is what I have, and it isn't working.

SET @sequence=0;
SELECT @sequence:=@sequence+1 AS sequence, name, id
  FROM students
  WHERE project LIKE '%science%'
  ORDER BY RAND();
SELECT @sequence;

Could somebody please help fix this?

Run1:

1   486 Linda
2   307 Patricia
3   218 Barbara
4   713 Mary

Run2:

1   713 Mary
2   486 Linda
3   307 Patricia
4   218 Barbara

Gratitude, Joanna




Aucun commentaire:

Enregistrer un commentaire