James Tookey

Full stack developer based in the Cotswolds, UK

GitHub profile
StackOverflow Profile
Twitter

Latest blogs

MySQL: Delete duplicates
19 Mar 2018 at 13:43
Imagick: Automatic line wrapping
28 Feb 2018 at 07:48
MySQL: Selecting one random item from each category
27 Feb 2018 at 15:57
27 February 2018

MySQL: Selecting one random item from each category

by James Tookey

I recently had a requirement to select a single item from each category in my database. However, with each select I needed to select a random item.

Code first, explanation is underneath:

SELECT * FROM (
    SELECT c.id AS category_id, c.category, i.id, i.name
    FROM categories c
    INNER JOIN items i ON c.id = i.category_id
    ORDER BY RAND()
) AS sorted_items
GROUP BY category_id

The randomisation is in a sub-query. This is because a GROUP BY statement always happens before an ORDER BY if they are part of the same query. If this happens, we would end up grouping by the category ID first which means we would not be able to randomly choose an item within that category.

tags: mysql