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;
ENDNote: 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.
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
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()
Method 2
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;Method 3
create schema so_gibberish; -- creates database
use so_gibberish; -- use it
-- drop table random; -- during debug
create table random
( id int auto_increment primary key,
question varchar(50) not null,
category int not null,
randomOrder int not null,
key (category)
);
-- drop table questions_for_user; -- during debug
create table questions_for_user
( physcOrder int auto_increment primary key,
id int not null,
question varchar(50) not null,
category int not null,
randomOrder int not null,
key (category)
);Create a stored procedure to insert random questions. It creates 300 at a time when you call it.
DELIMITER $$
drop procedure if exists createRandomQuestions$$
-- 17 categories of questions randomly created. yes random word questions and categories.
create procedure createRandomQuestions()
BEGIN
set @i=1;
WHILE @i<=300 DO
insert random (question,category) values ('xxx',1);
SELECT @lid:=LAST_INSERT_ID(); -- use id to seed, next 8 guaranteed different i think
UPDATE random SET question=concat(
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@lid)*4294967296))*36+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed)*36+1, 1), ' ?'
), category=floor(rand()*17+1),randomOrder=0
WHERE <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="127b762f527e7b76">[email protected]</a>;
set @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="6f06522f06">[email protected]</a>+1;
END WHILE;
END;
$$
DELIMITER ;call it:
call createRandomQuestions();
to check:
select category,count(*) from random group by category order by category; select count(*) from random; select * from random limit 10;
Now get three random questions for each category for a user upon request.
Come up with a good random SEEDING algorithm. The below is a stub, best to use an external source like http to a service, etc.
update random set randomOrder=rand()*unix_timestamp(); truncate table questions_for_user; insert into questions_for_user (id,question,category,randomOrder) select id,question,category,randomOrder from random order by rand(); select r.* FROM questions_for_user r WHERE ( SELECT COUNT(*) FROM random r1 WHERE r.category = r1.category AND r.id < r1.id ) <= 2 ORDER by r.category ASC, randomOrder; +------------+-----+------------+----------+-------------+ | physcOrder | id | question | category | randomOrder | +------------+-----+------------+----------+-------------+ | 297 | 266 | JNQH26DD ? | 1 | 841700408 | | 247 | 286 | ORK15577 ? | 1 | 980764662 | | 54 | 267 | T2HPRW88 ? | 1 | 1331420535 | | 190 | 275 | U5BFCUFF ? | 2 | 836160445 | | 192 | 285 | E3KDJ144 ? | 2 | 1166207975 | | 55 | 293 | GFWQ0BEE ? | 2 | 1356597807 | | 266 | 296 | 8MJCWR55 ? | 3 | 1121232849 | | 173 | 288 | 6GGOI2CC ? | 3 | 1209081435 | | 205 | 231 | LQMYMV44 ? | 3 | 1330946482 | | 103 | 289 | FUODJHJJ ? | 4 | 392498036 | | 274 | 295 | Y85VNBWW ? | 4 | 613800522 | | 204 | 280 | 441X1YTT ? | 4 | 1141251557 | | 162 | 273 | CC5FGKFF ? | 5 | 99041401 | | 36 | 252 | Y7V58ZEE ? | 5 | 1124240820 | | 143 | 234 | DWZULKBB ? | 5 | 1286225785 | | 15 | 276 | 3J1KGJZZ ? | 6 | 51285374 | | 265 | 294 | PTEMZ7GG ? | 6 | 649256602 | | 91 | 271 | TNG23R11 ? | 6 | 1393790410 | | 90 | 277 | DXE1W0GG ? | 7 | 622567356 | | 20 | 274 | LQTFS7RR ? | 7 | 1376505632 | | 121 | 279 | VQN6TVSS ? | 7 | 1428714367 | | 268 | 265 | A99UU1YY ? | 8 | 479923553 | | 242 | 284 | 5PDRR5YY ? | 8 | 658551160 | | 283 | 282 | NWZSPCZZ ? | 8 | 802197234 | | 114 | 233 | 3IHU1YKK ? | 9 | 118146688 | | 85 | 226 | BQTD6A44 ? | 9 | 292147382 | | 255 | 291 | YNT0YN11 ? | 9 | 559911752 | | 281 | 258 | IHK46577 ? | 10 | 196799301 | | 198 | 263 | SHEVXPBB ? | 10 | 545833955 | | 11 | 298 | QEHHSAEE ? | 10 | 1087006220 | | 60 | 250 | GEH9PVBB ? | 11 | 225193615 | | 270 | 272 | 3YY2EMM ? | 11 | 1143544695 | | 300 | 290 | O8B4WRCC ? | 11 | 1209867696 | | 68 | 246 | FTEEVJ00 ? | 12 | 608441021 | | 177 | 244 | X1JYHUBB ? | 12 | 1192309224 | | 208 | 240 | W771N588 ? | 12 | 1347800170 | | 249 | 300 | 97V6UXYY ? | 13 | 500846709 | | 100 | 292 | 71JZTMQQ ? | 13 | 607266604 | | 38 | 283 | WBQ0DGLL ? | 13 | 1187952919 | | 87 | 235 | MBPPXJ44 ? | 14 | 327445349 | | 178 | 242 | EZ1ET3ZZ ? | 14 | 852480639 | | 62 | 281 | EIHVH3ZZ ? | 14 | 1420114728 | | 95 | 251 | PT8E2CII ? | 15 | 72502146 | | 170 | 253 | 8L35PFYY ? | 15 | 1091765038 | | 65 | 287 | X5ZR3LHH ? | 15 | 1405199431 | | 138 | 278 | MBW03OUU ? | 16 | 84358922 | | 7 | 268 | 2G4G42WW ? | 16 | 1257379717 | | 1 | 299 | ZT8QRRMM ? | 16 | 1327297420 | | 221 | 297 | H00HLNRR ? | 17 | 890140945 | | 40 | 204 | O7VUW2NN ? | 17 | 1238474743 | | 122 | 229 | 3XEZG0PP ? | 17 | 1359337651 | +------------+-----+------------+----------+-------------+ 51 rows in set (0.01 sec)
Run it again for next user or same guy:
update random set randomOrder=rand()*unix_timestamp(); truncate table questions_for_user; insert into questions_for_user (id,question,category,randomOrder) select id,question,category,randomOrder from random order by rand(); select r.* FROM questions_for_user r WHERE ( SELECT COUNT(*) FROM random r1 WHERE r.category = r1.category AND r.id < r1.id ) <= 2 ORDER by r.category ASC, randomOrder; +------------+-----+------------+----------+-------------+ | physcOrder | id | question | category | randomOrder | +------------+-----+------------+----------+-------------+ | 112 | 286 | ORK15577 ? | 1 | 193281314 | | 176 | 266 | JNQH26DD ? | 1 | 530153269 | | 296 | 267 | T2HPRW88 ? | 1 | 891041924 | | 37 | 293 | GFWQ0BEE ? | 2 | 222852606 | | 239 | 285 | E3KDJ144 ? | 2 | 679635152 | | 262 | 275 | U5BFCUFF ? | 2 | 846163956 | | 95 | 288 | 6GGOI2CC ? | 3 | 1244253481 | | 81 | 231 | LQMYMV44 ? | 3 | 1380298624 | | 59 | 296 | 8MJCWR55 ? | 3 | 1420850554 | | 28 | 295 | Y85VNBWW ? | 4 | 806083444 | | 30 | 289 | FUODJHJJ ? | 4 | 814854070 | | 34 | 280 | 441X1YTT ? | 4 | 1119188021 | | 232 | 234 | DWZULKBB ? | 5 | 296692881 | | 295 | 273 | CC5FGKFF ? | 5 | 337915901 | | 140 | 252 | Y7V58ZEE ? | 5 | 644987638 | | 257 | 271 | TNG23R11 ? | 6 | 619359840 | | 27 | 294 | PTEMZ7GG ? | 6 | 869855570 | | 218 | 276 | 3J1KGJZZ ? | 6 | 1390090875 | | 64 | 279 | VQN6TVSS ? | 7 | 33942495 | | 290 | 277 | DXE1W0GG ? | 7 | 100007602 | | 173 | 274 | LQTFS7RR ? | 7 | 946909650 | | 291 | 265 | A99UU1YY ? | 8 | 107468716 | | 49 | 284 | 5PDRR5YY ? | 8 | 1068298164 | | 228 | 282 | NWZSPCZZ ? | 8 | 1115906220 | | 284 | 226 | BQTD6A44 ? | 9 | 45678738 | | 179 | 291 | YNT0YN11 ? | 9 | 700305900 | | 164 | 233 | 3IHU1YKK ? | 9 | 1416089612 | | 193 | 258 | IHK46577 ? | 10 | 460111512 | | 214 | 298 | QEHHSAEE ? | 10 | 482322673 | | 7 | 263 | SHEVXPBB ? | 10 | 766681927 | | 178 | 290 | O8B4WRCC ? | 11 | 341509950 | | 31 | 272 | 3YY2EMM ? | 11 | 726662739 | | 297 | 250 | GEH9PVBB ? | 11 | 1386568968 | | 32 | 240 | W771N588 ? | 12 | 303493686 | | 283 | 246 | FTEEVJ00 ? | 12 | 710591266 | | 177 | 244 | X1JYHUBB ? | 12 | 916685336 | | 212 | 283 | WBQ0DGLL ? | 13 | 595739692 | | 159 | 300 | 97V6UXYY ? | 13 | 688431139 | | 52 | 292 | 71JZTMQQ ? | 13 | 1039681379 | | 56 | 235 | MBPPXJ44 ? | 14 | 109832248 | | 207 | 242 | EZ1ET3ZZ ? | 14 | 418951740 | | 6 | 281 | EIHVH3ZZ ? | 14 | 1182157711 | | 149 | 287 | X5ZR3LHH ? | 15 | 364819476 | | 88 | 251 | PT8E2CII ? | 15 | 673475236 | | 123 | 253 | 8L35PFYY ? | 15 | 1204512525 | | 190 | 278 | MBW03OUU ? | 16 | 641720378 | | 160 | 299 | ZT8QRRMM ? | 16 | 1289470813 | | 75 | 268 | 2G4G42WW ? | 16 | 1427431541 | | 202 | 297 | H00HLNRR ? | 17 | 374047531 | | 243 | 204 | O7VUW2NN ? | 17 | 951026810 | | 300 | 229 | 3XEZG0PP ? | 17 | 1319302198 | +------------+-----+------------+----------+-------------+ 51 rows in set (0.01 sec)
So the questions are different.
To get the last results in a shuffled sense:
select r.* FROM questions_for_user r WHERE ( SELECT COUNT(*) FROM random r1 WHERE r.category = r1.category AND r.id < r1.id ) <= 2 ORDER by randomOrder; +------------+-----+------------+----------+-------------+ | physcOrder | id | question | category | randomOrder | +------------+-----+------------+----------+-------------+ | 64 | 279 | VQN6TVSS ? | 7 | 33942495 | | 284 | 226 | BQTD6A44 ? | 9 | 45678738 | | 290 | 277 | DXE1W0GG ? | 7 | 100007602 | | 291 | 265 | A99UU1YY ? | 8 | 107468716 | | 56 | 235 | MBPPXJ44 ? | 14 | 109832248 | | 112 | 286 | ORK15577 ? | 1 | 193281314 | | 37 | 293 | GFWQ0BEE ? | 2 | 222852606 | | 232 | 234 | DWZULKBB ? | 5 | 296692881 | | 32 | 240 | W771N588 ? | 12 | 303493686 | | 295 | 273 | CC5FGKFF ? | 5 | 337915901 | | 178 | 290 | O8B4WRCC ? | 11 | 341509950 | | 149 | 287 | X5ZR3LHH ? | 15 | 364819476 | | 202 | 297 | H00HLNRR ? | 17 | 374047531 | | 207 | 242 | EZ1ET3ZZ ? | 14 | 418951740 | | 193 | 258 | IHK46577 ? | 10 | 460111512 | | 214 | 298 | QEHHSAEE ? | 10 | 482322673 | | 176 | 266 | JNQH26DD ? | 1 | 530153269 | | 212 | 283 | WBQ0DGLL ? | 13 | 595739692 | | 257 | 271 | TNG23R11 ? | 6 | 619359840 | | 190 | 278 | MBW03OUU ? | 16 | 641720378 | | 140 | 252 | Y7V58ZEE ? | 5 | 644987638 | | 88 | 251 | PT8E2CII ? | 15 | 673475236 | | 239 | 285 | E3KDJ144 ? | 2 | 679635152 | | 159 | 300 | 97V6UXYY ? | 13 | 688431139 | | 179 | 291 | YNT0YN11 ? | 9 | 700305900 | | 283 | 246 | FTEEVJ00 ? | 12 | 710591266 | | 31 | 272 | 3YY2EMM ? | 11 | 726662739 | | 7 | 263 | SHEVXPBB ? | 10 | 766681927 | | 28 | 295 | Y85VNBWW ? | 4 | 806083444 | | 30 | 289 | FUODJHJJ ? | 4 | 814854070 | | 262 | 275 | U5BFCUFF ? | 2 | 846163956 | | 27 | 294 | PTEMZ7GG ? | 6 | 869855570 | | 296 | 267 | T2HPRW88 ? | 1 | 891041924 | | 177 | 244 | X1JYHUBB ? | 12 | 916685336 | | 173 | 274 | LQTFS7RR ? | 7 | 946909650 | | 243 | 204 | O7VUW2NN ? | 17 | 951026810 | | 52 | 292 | 71JZTMQQ ? | 13 | 1039681379 | | 49 | 284 | 5PDRR5YY ? | 8 | 1068298164 | | 228 | 282 | NWZSPCZZ ? | 8 | 1115906220 | | 34 | 280 | 441X1YTT ? | 4 | 1119188021 | | 6 | 281 | EIHVH3ZZ ? | 14 | 1182157711 | | 123 | 253 | 8L35PFYY ? | 15 | 1204512525 | | 95 | 288 | 6GGOI2CC ? | 3 | 1244253481 | | 160 | 299 | ZT8QRRMM ? | 16 | 1289470813 | | 300 | 229 | 3XEZG0PP ? | 17 | 1319302198 | | 81 | 231 | LQMYMV44 ? | 3 | 1380298624 | | 297 | 250 | GEH9PVBB ? | 11 | 1386568968 | | 218 | 276 | 3J1KGJZZ ? | 6 | 1390090875 | | 164 | 233 | 3IHU1YKK ? | 9 | 1416089612 | | 59 | 296 | 8MJCWR55 ? | 3 | 1420850554 | | 75 | 268 | 2G4G42WW ? | 16 | 1427431541 | +------------+-----+------------+----------+-------------+ 51 rows in set (0.01 sec)
The reason the other answers seem to be returning the same questions (top 3 or bottom 3) imo is because of the physical ordering of the table, which is why I created the 2nd table. The second table uses a new bogus physical ordering and the insert into it is via order by random (so the physical order problem doesn’t exist). Hope that makes sense.
And of course I poached the answer from pjanaway and couldn’t get Gordon’s to work on my system though I like his variable approach.
Good luck.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0