How to get RANDOM records from each category in MySQL?

maytham-ɯɐɥʇʎɐɯ

In my MySQL database, I have a table with different questions in different categories.

I would like to write a SQL statement that returns 3 RANDOM questions of EACH category.

Here is an example of database records:

id  question    category
1   Question A  1
2   Question B  1
3   Question C  1
4   Question D  1
5   Question D  1
6   Question F  2
7   Question G  2
8   Question H  2
9   Question I  2
10  Question J  2
11  Question K  3
12  Question L  3
13  Question M  3
14  Question N  3
15  Question O  3
16  Question P  3

Here is output/results of 3 Random selected and shuffled from all questions of each category from the above list:

2   Question B  1
4   Question D  1
3   Question C  1
10  Question J  2
7   Question G  2
9   Question I  2
11  Question K  3
15  Question P  3
13  Question M  3

I have so far played with the following statement for testing:

SELECT * FROM `random` ORDER BY RAND() LIMIT 0,3;

This return only 3 RANDOM questions from all categories.

And I have afterwards looked for example at this link: MYSQL select random of each of the categories

And tried this:

(SELECT * FROM `random` WHERE category = 1 ORDER BY RAND() LIMIT 3)
UNION ALL
(SELECT * FROM `random` WHERE category = 2 ORDER BY RAND() LIMIT 3)
UNION ALL
(SELECT * FROM `random` WHERE category = 3 ORDER BY RAND() LIMIT 3)

But here I need to add each category manually.

My Question: I was a wonder if it is at all possible to fetch 3 RANDOM records/rows from each category of all categories (automatically)?


EDIT

This is not part of the question but help.

Dummy data creator The query code will table called random and created a stored procedure called create_random and when you run the stored procedure, it will create random dummy data inside a random table:

DELIMITER $$
DROP TABLE IF EXISTS `random`;
DROP PROCEDURE IF EXISTS `create_random` $$

CREATE TABLE `random` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `question` VARCHAR(50) NULL DEFAULT NULL,
    `category` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=401
;

CREATE DEFINER=`root`@`localhost`
PROCEDURE `create_random`()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''

BEGIN

DECLARE v_max int unsigned DEFAULT 100;
DECLARE v_counter int unsigned DEFAULT 0;
DECLARE cat_counter int unsigned DEFAULT 0;

  TRUNCATE TABLE `random`;
  START TRANSACTION;
  WHILE v_counter < v_max DO
    IF v_counter %10=0 THEN SET cat_counter=cat_counter+1;
    END IF;
    INSERT INTO `random` (question, category) VALUES ( CONCAT('Question', FLOOR(0 + (RAND() * 65535))), cat_counter );
    SET v_counter=v_counter+1;
  END WHILE;
  COMMIT;
END

Note: I tried all answers and all works fine. Gordon Linoff and pjanaway answer select RANDOM only from top 3 or bottom 3 questions, I have checked Gordon answer because he answered first, but that does not mean other answers is not good, all of them are good and it is up to users to pick the right answer or combination of answers. I love all the answers and vote them up. Drew Pierce answer newly to this question, it is more interesting right now and almost near the goal. Thanks to all.

Gordon Linoff

Yes, you can do this by enumerating the rows and then fetching the top three:

select r.id, r.question, r.category
from (select r.*,
             (@rn := if(@c = category, @rn + 1,
                        if(@c := category, 1, 1)
                       )
             ) as seqnum
      from `random` r cross join
           (select @rn := 0, @c := -1) params
      order by category, rand()
     ) r
where seqnum <= 3;

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

How can I get 5 records for each 'category' with one select in MySQL?

From Dev

Get random records in MySql

From Dev

Get the Top records from each group in MYSQL

From Dev

How to get random records from a viewmodel

From Dev

Oracle SQL: How to get Random Records by each group with predefined contribution

From Dev

Limit 10 records from posts for each category

From Dev

How to retrieve one random row for each category?

From Dev

Count records in each category

From Dev

MySql taking random element from distinct random category

From Dev

select latest 20 records under each category from one table

From Dev

Pandas groupby category, rating, get top value from each category?

From Dev

How to get the number of orders for each category?

From Dev

Is there a way to get the splitting records and attributes for each tree in random forest in R?

From Dev

MySQL - How to get counts of specific value from all the records

From Dev

How to get the timestamp of individual records from mysql databse php?

From Dev

how to get all the records from MySQL table in php web services

From Dev

Mysql - get last post from category

From Dev

How to get ratio of sums 10% top and bottom each category from table?

From Dev

How to quickly SELECT 3 random records from a 30k MySQL table with a where filter by a single query?

From Dev

How to quickly SELECT 3 random records from a 30k MySQL table with a where filter by a single query?

From Dev

How can I get all records of taxonomy category in WordPress Query?

From Dev

Laravel 5 - how to get a random row from first 30 records in eloquent?

From Dev

Laravel 5 - how to get a random row from first 30 records in eloquent?

From Dev

How to get random rows in mysql

From Dev

display last 2 entries in each category from a mysql table

From Dev

display last 2 entries in each category from a mysql table

From Dev

How to get the category name from category slug in wordpress?

From Dev

How to get records from SQL

From Dev

How to get records from SQL

Related Related

  1. 1

    How can I get 5 records for each 'category' with one select in MySQL?

  2. 2

    Get random records in MySql

  3. 3

    Get the Top records from each group in MYSQL

  4. 4

    How to get random records from a viewmodel

  5. 5

    Oracle SQL: How to get Random Records by each group with predefined contribution

  6. 6

    Limit 10 records from posts for each category

  7. 7

    How to retrieve one random row for each category?

  8. 8

    Count records in each category

  9. 9

    MySql taking random element from distinct random category

  10. 10

    select latest 20 records under each category from one table

  11. 11

    Pandas groupby category, rating, get top value from each category?

  12. 12

    How to get the number of orders for each category?

  13. 13

    Is there a way to get the splitting records and attributes for each tree in random forest in R?

  14. 14

    MySQL - How to get counts of specific value from all the records

  15. 15

    How to get the timestamp of individual records from mysql databse php?

  16. 16

    how to get all the records from MySQL table in php web services

  17. 17

    Mysql - get last post from category

  18. 18

    How to get ratio of sums 10% top and bottom each category from table?

  19. 19

    How to quickly SELECT 3 random records from a 30k MySQL table with a where filter by a single query?

  20. 20

    How to quickly SELECT 3 random records from a 30k MySQL table with a where filter by a single query?

  21. 21

    How can I get all records of taxonomy category in WordPress Query?

  22. 22

    Laravel 5 - how to get a random row from first 30 records in eloquent?

  23. 23

    Laravel 5 - how to get a random row from first 30 records in eloquent?

  24. 24

    How to get random rows in mysql

  25. 25

    display last 2 entries in each category from a mysql table

  26. 26

    display last 2 entries in each category from a mysql table

  27. 27

    How to get the category name from category slug in wordpress?

  28. 28

    How to get records from SQL

  29. 29

    How to get records from SQL

HotTag

Archive