How to randomly select a row from each group in Mysql

Question

Suppose I have such a table:

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 that 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 shuffled and grouped. How can I do this?

PS1: Please note that: - time group x is associated with item d and formerly with item f; - time group y is associated with item b and formerly item c; - time group z is associated with item a and once with item, and;

PS2: If it is possible to follow the line of reasoning of 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 don’t exist in Mysql. So I need help anyway.

Answer 1

Solving

A way of doing:

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 sub-query searching randomly in the same table, referencing the group, thus generating a random list.

In this generated list, group by grupo, and random order.

---

Remarks

1. If you execute part of the sub-query, will see that it will bring the times duplicated, but it does not matter, because the last query that will be the final filter.

2. I replaced the field name group for grupo to be able to simulate as in some scenarios may give error due group be a reserved word.

---

See worked on: SQL Fiddle or DB Fiddle