Query using RAND but following a defined structure

user_13486017 0
Viewed 33

I have the following problem and I would like to know how to solve it creating a query on MySQL.

Check out those two tables:

enter image description here

enter image description here

I need to follow a structure to show the results I want. It is like a randon inside a randon. For example, I want to randomize each section, but ids 1 to 3 must be together and then randomize too.

enter image description here

How would be a query to get such results?

mysql
1 Answers

With this query:

select id, id_start, id_end, rand() rnd
from table2
group by id, id_start, id_end

you can return a random number for each of the rows of table2.
Join this query to table1 and sort the result first by that random number and then by random:

select t1.id, t1.description
from table1 t1 
inner join (
  select id, id_start, id_end, rand() rnd
  from table2
  group by id, id_start, id_end
) t2
on t1.id between t2.id_start and t2.id_end
order by t2.rnd, rand()

See the demo.

user_104988280
Related