MySQL Randomize within Random Results

Question

Ok, Assuming I have following Table

=================
gr         name
=================
A          John
A          Mary
A1         Jack
A1         Stephen
A          Jess
A2         Neil
A2         Chris

what I would like to do is Random the order results with one rule, A1 should stick with A1 (in Random Order), and A2 should stick with A2 (in Random Order).

=================
gr         name
=================
A          Mary
A2         Chriss
A2         Neil
A          Jess
A          John
A1         Stephen
A1         Jack

The group name is Free, if you can comes up with a better group name, feel free to use it.

Thanks.

edit : For those who is curious, what I want to create is an Online Quiz. It will present question in Random Order to minimize the cheating. With one rule, a Question with Paragraph Text should stick with each other (in random order) so the students won't have to read the same paragraph multiple times.

The "paragraph grouped question" is in random order among other non-paragraph questions.

If you have a better idea of doing this, feel free to answer.

I have found this link : MySQL order by rand() grouped by day

I think my case is similar to his. It's just that he want to Pick One, and I want to pick ALL.

Another query I tried

SELECT * FROM `tbl` ORDER BY gr, rand()

it is able to randomize the name column, and the gr is stick to each other. I am left with one question, how to randomize the order of gr column while still make A1, A2, stick to each other

What have you tried? How about showing some SQL you've attempted? Note that group is not a legal MySQL column name. — msanford
– msanford, Commented Jan 30, 2016 at 4:24
it's just an example. i have tried this stackoverflow.com/questions/3188921/… but this is not what i am looking for — Yokowasis
– Yokowasis, Commented Jan 30, 2016 at 4:26

Fabricator · Accepted Answer · 2016-01-30 05:06:55Z

1

Here's another way to do it. The trick is generate 2 random numbers in the beginning for A1 and A2, and use them in the sort:

select a.*
from tbl a
join (select @a1:=rand(), @a2:=rand()) b
order by case gr 
         when 'A1' then @a1
         when 'A2' then @a2
         else rand() end, rand();

fiddle

answered Jan 30, 2016 at 5:06

Fabricator

12.8k2 gold badges29 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yokowasis Over a year ago

Neat, Just what I need

Plínio Almeida · Accepted Answer · 2016-01-30 04:50:41Z

0

I would do something like that:

SELECT 
    T.*
FROM 
    TBL T
    INNER JOIN (
        SELECT 
            GR, 
            RAND() AS R
        FROM 
            (SELECT DISTINCT GR FROM TBL) X
    ) T2
        ON T.GR = T2.GR
ORDER BY 
    T2.R,
    RAND()

answered Jan 30, 2016 at 4:50

Plínio Almeida

635 bronze badges

Collectives™ on Stack Overflow

MySQL Randomize within Random Results

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related