0

Hi I am doing a program for give question, but this questions need be differents for differents user now I need generate questions random in mysql try to use RAND() but i can't make that this work fine for my requirement, this don't work:

SELECT * FROM oscuro.answers
INNER JOIN questions ON answers.question_id = questions.id
INNER JOIN sub_categories ON questions.sub_category_id = sub_categories.id
WHERE sub_categories.category_id = 1 AND questions.id > RAND();

I need questions.ida number random of all records on the table questions, this should give a random question with all answers of this question. how I generate random number only for that field?

CoolLife
  • 1,419
  • 4
  • 17
  • 43
  • The answer to your question is below. However, doing it this way is not necessarily the best way to solve your problem. This question has some good ideas: http://stackoverflow.com/questions/211329/quick-selection-of-a-random-row-from-a-large-table-in-mysql/213242#213242 Particularly ```SELECT * ... ORDER BY rand() LIMIT x``` is a quick and easy way to solve your problem. – everybody Nov 04 '16 at 04:10

1 Answers1

0

You can do a join on a subquery. A simple example:

> SELECT * FROM `foo`;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
> SELECT * FROM foo 
>   INNER JOIN 
>   (SELECT RAND() as random_number)
>     as sqy;

+----+---------------------+
| id | random_number       |
+----+---------------------+
|  1 | 0.16365532499147142 |
|  2 | 0.16365532499147142 |
|  3 | 0.16365532499147142 |
|  4 | 0.16365532499147142 |
|  5 | 0.16365532499147142 |
+----+---------------------+

So then in your case

> SELECT * FROM oscuro.answers
>   INNER JOIN questions
>     ON answers.question_id = questions.id
>   INNER JOIN sub_categories
>     ON questions.sub_category_id = sub_categories.id
>   INNER JOIN (SELECT RAND() as random_number) as sqy
> WHERE
>   sub_categories.category_id = 1
> AND
>   questions.id > random_number;
everybody
  • 328
  • 1
  • 9
  • this give me all record of the table question, for my test just have 5 records on my table and this give the 5 records of question just need 1. – CoolLife Nov 04 '16 at 04:16
  • Right. That is because RAND() generates a random number between 0 and 1. For your test you want a random number between 1 and 5. So do RAND() * 5 Or more generally replace the whole subquery with ```(SELECT CEIL(COUNT(1) * RAND()) FROM foo)``` (but that will only really work if you have no holes in your id space) --- it really is better to do the ```ORDER BY RAND() LIMIT 1```. – everybody Nov 04 '16 at 04:25