How can I return a random row for each section in a MySQL data set that has non-unique values

Question

Given the dataset

+-----------+---------+
| device_id | section |
+-----------+---------+
| 100       | A       |
+-----------+---------+
| 101       | B       |
+-----------+---------+
| 102       | B       |
+-----------+---------+
| 103       | C       |
+-----------+---------+
| 104       | D       |
+-----------+---------+
| 105       | C       |
+-----------+---------+
| 106       | A       |
+-----------+---------+
| 107       | C       |
+-----------+---------+

   

What MySQL query will produce one (and only one) row for each section, selected at random.

   

Expected results (random variation):

+-----------+---------+
| device_id | section |
+-----------+---------+
| 106       | A       |
+-----------+---------+
| 101       | B       |
+-----------+---------+
| 105       | C       |
+-----------+---------+
| 104       | D       |
+-----------+---------+

I have tried several variations using GROUP BY and DISTINCT but have not had any success.

--edit-- I updated the title to clarify that I am not asking that a single row be returned, but that I get one, and only one device_id for each distinct section.

Answer 1

Use group_concat() to create a comma separated list of all the device_ids of each section in random order and pick the 1st with substring_index():

select substring_index(group_concat(device_id order by rand()), ',', 1) device_id,
       section
from tablename
group by section

See the demo.

For MySql 8.0+ you can use ROW_NUMBER() window function:

select device_id, section
from (
  select *, row_number() over (partition by section order by rand()) rn
  from tablename
) t
where rn = 1

See the demo.