How to combine results of two select, UNION All, rand()

There is table, which contains some answers. One of answers is true (field tf=1), and several are false (field tf=0). How to select 1 true answer and N false random answers? The result must be in random order. This is my example, it works, but true answer always in first row:

(SELECT * FROM answers where tf=1 and `questionsid`=1 limit 1)
UNION all
(SELECT * FROM answers where tf=0 and `questionsid`=1 order by rand() limit 5 )

2 answers

  • answered 2021-09-11 20:08 Stu

    Without any sample data this is untested however presumably you can wrap your query with an outer select, so after selecting the one right answer and 5 additional random wrong answers, return all 6 again ordered randomly:

    select * from (
        (select * 
        from answers 
        where tf=1 and questionsid=1 
        limit 1)
        union all
        (select * 
        from answers 
        where tf=0 and questionsid=1 
        order by Rand() 
        limit 5)
    )r
    order by rand()
    

  • answered 2021-09-12 09:18 Lookhome

    Thanks. By the way, I have another solution, which also works:

    (SELECT *, rand() as t FROM answers where tf=1 and `questionsid`=1 limit 1)
    UNION all
    (SELECT *, rand() as t FROM answers where tf=0 and `questionsid`=1 order by rand() limit 5 )
    order by t
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum