MYSQL Select One Random record from each Category

by

This query returns all items joined to categories in random order:

SELECT
c.id AS cid, c.category, i.id AS iid, i.name
FROM categories c
INNER JOIN items i ON c.id = i.category
ORDER BY RAND()

To restrict each category to one, wrap the query in a partial GROUP BY:

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

Note that when a query has both GROUP BY and ORDER BY clause, the grouping is performed before sorting. This is why I have used two queries: the first one sorts the results, the second one groups the results.

I understand that this query isn’t going to win any race. I am open to suggestions.

More Related Contents:

  1. MySQL select 10 random rows from 600K rows fast
  2. MySQL: Alternatives to ORDER BY RAND()
  3. quick selection of a random row from a large table in mysql
  4. How does MySQL process ORDER BY and LIMIT in a query?
  5. Ordering by the order of values in a SQL IN() clause
  6. Ordering by specific field value first
  7. MySQL “Group By” and “Order By”
  8. Using union and order by clause in mysql
  9. SQL order string as number
  10. Simple Random Samples from a Sql database
  11. MySQL order by “best match”
  12. Order by COUNT per value
  13. MySQL/MariaDB – order by inside subquery
  14. How to select one row randomly taking into account a weight?
  15. quick selection of a random row from a large table in mysql
  16. Simple Random Samples from a MySQL Sql database
  17. Mysql: Order by like?
  18. Sort the rows according to the order specified in WHERE IN clause
  19. Insert/ Update random date in MySQL
  20. ORDER BY alphabet first then follow by number
  21. mysql unique number generation
  22. i have an error which i dont understand. can you help me? [closed]
  23. Simulate lag function in MySQL
  24. Get record counts for all tables in MySQL database
  25. Use MySQL spatial extensions to select points inside circle
  26. MySQL create stored procedure syntax with delimiter
  27. How to find non-existing data from another Table by JOIN?
  28. Strings as Primary Keys in MYSQL Database [closed]
  29. Select data from “show tables” MySQL query
  30. What is dynamic SQL?

Leave a Comment