MySQL selecting random rows with one unique row within one query

Question

Is it possible to select x random rows from a table, where one of the rows have to be a special row (thereby with a special field value) within one query?

Basically what I'm trying to create is a Guessing Game, where you have x amount of questions, with x amount of possible (checkbox selectable) answers!

This is how I select the answers currently... With 2 query's

$answers = 4; // This is the max amount of answers

// Just for testing the question_id is manually set

$query = "SELECT * FROM `answers` WHERE `question_id` = 0 AND `correct` != 1 ORDER BY RAND() LIMIT " . ($answers - 1) . "";
$query = "SELECT * FROM `answers` WHERE `question_id` = 0 AND `correct` = 1 LIMIT 1";

The "question_id" tells which question we are talking about, and the "correct" just tells if that field is the correct answer

So is it possible to select x random rows from a table, where one of the rows have to be "the correct one", within one query?

Did you try using the UNION clause?

Sparky
– Sparky

2013-07-14 03:18:18 +00:00
Commented Jul 14, 2013 at 3:18 — Sparky
– Sparky, Commented Jul 14, 2013 at 3:18

cooshal · Accepted Answer · 2013-07-14 03:40:43Z

1

may be use UNION ?

$answers = 4;

$query = "SELECT * FROM `answers` WHERE `question_id` = 0 AND `correct` != 1 ORDER BY RAND() LIMIT " . ($answers - 1) . "";

$query .= " UNION ";

$query .= "SELECT * FROM `answers` WHERE `question_id` = 0 AND `correct` = 1 LIMIT 1";

With one single query, you will be able to get the desired result

http://dev.mysql.com/doc/refman/5.0/en/union.html

answered Jul 14, 2013 at 3:40

cooshal

7982 gold badges8 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

vallentin Over a year ago

Damn, that gave me this error... Incorrect usage of UNION and ORDER BY

vallentin Over a year ago

Ohh it needed to have parenthesis around each "query"

David · Accepted Answer · 2013-07-14 03:18:53Z

0

$query = "SELECT * FROM answers WHERE ... whatever ... AND `question_id` IN (5, 9, 12, 16, 2)

So, you first find the number of rows and then put the correct answer's ID in to the WHERE IN statement. Because you know the one you want, you can just surround it with random IDs from the table and then render them in a random fashion.

answered Jul 14, 2013 at 3:18

David

3,8112 gold badges31 silver badges38 bronze badges

3 Comments

vallentin Over a year ago

No, I don't know the answer's id, the only thing I know is the question_id and the amount of rows (answers) I want in return

David Over a year ago

Then generate random numbers inside the number of rows that exist

vallentin Over a year ago

So you want me to make another query, to check for how many rows that exist, and then... well that didn't make it become only one query?

Collectives™ on Stack Overflow

MySQL selecting random rows with one unique row within one query

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related