Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get RANDOM records from each category in MySQL?

Tags:

sql

mysql

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.

like image 870
Maytham Avatar asked Jul 18 '15 20:07

Maytham


2 Answers

In addition to the other answer, this is also another way to do it.

SELECT r.* FROM random r
WHERE (
  SELECT COUNT(*) FROM random r1
  WHERE r.category = r1.category AND r.id < r1.id
) <= 2
ORDER BY r.category ASC, RAND()
like image 127
pjau Avatar answered Sep 28 '22 11:09

pjau


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;
like image 43
Gordon Linoff Avatar answered Sep 28 '22 09:09

Gordon Linoff