How to randomly select a row from each group in MySQL

1

Suppose I have a table like this:

item   | group
-------+--------
item a | group z
item b | group y
item c | group y
item d | group x
item e | group z
item f | group x

And now I want to randomly select a single item to represent each group, so that after random reordering and grouping, the result looks like this:

item   | group
-------+--------
item b | group y
item d | group x
item e | group z

Or so:

item   | group
-------+--------
item b | group y
item a | group z
item d | group x

Or so:

item   | group
-------+--------
item f | group x
item e | group z
item c | group y

All scrambled and grouped. How can I do this?

PS1: Note that: - time the group x is associated with the item d and formerly to item f; - time the group y is associated to item b and formerly to item c; - time group z is associated to item a and to item e;

PS2: If it is possible to follow the line of reasoning for this post here, it is better: Select the first row of each group in MySQL otherwise, no problem at all!

PS3: I've seen similar explanations using the HOVER and PARTITION command. But these commands do not exist in MySQL. So I really need help.

    
asked by anonymous 05.09.2018 / 03:15

1 answer

1

Solving

One way to do it:

select (select t2.item from tabela t2 
        where t2.grupo = t1.grupo order by rand() limit 1) as item,
t1.grupo
from tabela t1
group by grupo
order by rand()

Explaining

What I did was a sub-query that randomly searches the same table, referencing the group , thus generating a random list .

In this generated list, grouped by grupo , and ordered randomly .

Observations

  • If you run the sub-query , you will see that it will bring the duplicate times, but it does not matter, since the last query final filter.

  • See worked in: SQL Fiddle or DB Fiddle

        
    07.09.2018 / 12:07