CODEWITHSUNDEEP
sqlsqliteandroid-sqlite
Deno Agüero
Deno Agüero

Reputation: 599

Using Random function in the WHERE Clause

In my WHERE Clause I'm using the random function to get a random number between 1 and 5. However the result is always empty without any error.

Here it is:

Select Question._id, question_text, question_type, topic, favorite,   
picture_text, picture_src, video_text, video_src, info_title, info_text, 
info_picture_src, topic_text
FROM Question 
LEFT JOIN Question_Lv ON Question._id = Question_Lv.question_id 
LEFT JOIN Info ON Question._id = Info.question_id 
LEFT JOIN Info_Lv ON Question._id = Info_Lv.question_id 
LEFT JOIN Picture ON Question._id = Picture.question_id 
LEFT JOIN Picture_Lv ON Question._id = Picture_Lv.question_id 
LEFT JOIN Video ON Question._id = Video.question_id 
LEFT JOIN Video_Lv ON Question._id = Video_Lv.question_id 
LEFT JOIN Topic ON Question.topic = Topic._id 
LEFT JOIN Topic_Lv ON Topic._id = Topic_Lv.topic_id 
LEFT JOIN Exam ON Question._id = Exam.question_id 
WHERE Exam.exam = (random() * 5+ 1)

What is the random function doing in this case and how to use it correctly?

Upvotes: 2

Views: 441

Answers (2)

marcothesane
marcothesane

Reputation: 6731

I'll answer this question using Vertica as a database.

Vertica has the function RANDOM(), which returns a random double precision number between 0 and 1, and the function RANDOMINT(<*integer*>), which returns an integer number between 0 and *<integer>*-1. I'll use RANDOMINT(5) for this example.

As a general suggestion - Isolate your specific problem in your question. The joins in your query are not part of the problem. And use a sample table, like I do in the code below.

As some of the previous answers suggested, RANDOMINT(5) will return a new random integer between 0 and 4 for each of the rows that are read from the exam table.

See here:

WITH exam(id,exam,exam_res) AS (
          SELECT  1,1,'exam_res_1' 
UNION ALL SELECT  2,2,'exam_res_2' 
UNION ALL SELECT  3,3,'exam_res_3' 
UNION ALL SELECT  4,4,'exam_res_4' 
UNION ALL SELECT  5,5,'exam_res_5' 
UNION ALL SELECT  6,1,'exam_res_1' 
UNION ALL SELECT  7,2,'exam_res_2' 
UNION ALL SELECT  8,3,'exam_res_3' 
UNION ALL SELECT  9,4,'exam_res_4' 
UNION ALL SELECT 10,5,'exam_res_5'
UNION ALL SELECT 11,1,'exam_res_1'
UNION ALL SELECT 12,2,'exam_res_2'
UNION ALL SELECT 13,3,'exam_res_3'
UNION ALL SELECT 14,4,'exam_res_4'
UNION ALL SELECT 15,5,'exam_res_5'
)
SELECT * FROM exam WHERE exam=RANDOMINT(5)+1
;
id|exam|exam_res
 3|   3|exam_res_3
 4|   4|exam_res_4
 5|   5|exam_res_5
 6|   1|exam_res_1
 7|   2|exam_res_2
 9|   4|exam_res_4
12|   2|exam_res_2

What you need to do is make sure that you call your random number generator only once.

If your database abides to the ANSI 99 standard and supports the WITH clause (the common table expression, as I also use it to generate the sample data), do that also in a common table expression - which I call search_exam:

WITH search_exam(exam) AS (
  SELECT RANDOMINT(5)+1
)
,    exam(id,exam,exam_res) AS (
          SELECT  1,1,'exam_res_1' 
UNION ALL SELECT  2,2,'exam_res_2' 
UNION ALL SELECT  3,3,'exam_res_3' 
UNION ALL SELECT  4,4,'exam_res_4' 
UNION ALL SELECT  5,5,'exam_res_5' 
UNION ALL SELECT  6,1,'exam_res_1' 
UNION ALL SELECT  7,2,'exam_res_2' 
UNION ALL SELECT  8,3,'exam_res_3' 
UNION ALL SELECT  9,4,'exam_res_4' 
UNION ALL SELECT 10,5,'exam_res_5'
UNION ALL SELECT 11,1,'exam_res_1'
UNION ALL SELECT 12,2,'exam_res_2'
UNION ALL SELECT 13,3,'exam_res_3'
UNION ALL SELECT 14,4,'exam_res_4'
UNION ALL SELECT 15,5,'exam_res_5'
)
SELECT id,exam,exam_res FROM exam 
    WHERE exam=(SELECT exam FROM search_exam)
;
id|exam|exam_res
 1|   1|exam_res_1
 6|   1|exam_res_1
11|   1|exam_res_1

Alternatively, you can go SELECT id,exam,exam_res FROM exam INNER JOIN search_exam USING(exam) .

Happy playing - Marco the Sane

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

From Docs

random()

The random() function returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807.

Hence your random value is not between 0 and 1 as you assumed and hence no rows.

You can get it between 0 and 1 by dividing it with 2×9223372036854775808 and adding 0.5 to it.

random() / 18446744073709551616 + 0.5

So, your where clause becomes:

WHERE Exam.exam = ((random() / 18446744073709551616 + 0.5) * 5 + 1)

which is same as:

WHERE Exam.exam = 5 * random() / 18446744073709551616 + 3.5

Also, you'll probably need to round the output of right side calculation, so:

WHERE Exam.exam = round(5 * random() / 18446744073709551616 + 3.5)

Upvotes: 2

Related Questions