13

I am developing a quiz website, and I have a database which stores all of the questions. There are different types of quizzes, like math, science, history, etc. All of the questions are stored in one table.

My questions table looks like this:

questions ( qno(int)  ,type(int), question, .... ,... )

qno is the primary key, and type is used to keep track of the quiz type.:

if type = 1 (math)
 type = 2(science)

Now, I want to select some random questions for each type of test. For example, I may want to select some random 20 questions for only the math test.

Does MySQL have some way to select random rows?

Eric
  • 84,123
  • 11
  • 110
  • 115
user156073
  • 1,695
  • 8
  • 29
  • 37
  • dup of [How to request a random row in SQL?](http://stackoverflow.com/q/19412/), [quick selection of a random row from a large table in mysql](http://stackoverflow.com/q/211329/90527). – outis Mar 30 '12 at 03:45
  • [MySQL select 10 random rows from 600K rows fast](https://stackoverflow.com/a/4329447/6521116) – LF00 Jun 17 '17 at 11:13

4 Answers4

31

You can use the rand function in MySQL to order the rows, and then take the top 10 (or however many you want) with limit.

select * from table order by rand() limit 10

If you want just the math questions:

select * from table where type = 1 order by rand() limit 10
Eric
  • 84,123
  • 11
  • 110
  • 115
  • see i want to show user one question at a time. So i am thinking is that i will use the above query when the quiz starts and then i will store all the question no in session and then i will fectch each question by using a new quesry every time . Is it right approach? – user156073 Aug 16 '09 at 07:34
  • If you want just one question per page, just do `limit 1` instead of `limit 10`. – Eric Aug 16 '09 at 15:26
  • Store the questions that that user has seen in a table and then construct this query left joining to that table and where the seen table's field is null. – Unsliced Aug 19 '09 at 10:24
  • 1
    You can use php sessions to maintain a history of questions asked in that session. Each time you query for random question, add a "WHERE QuestionID NOT IN ( previously_asked_question_ids )" clause. – Salman A Aug 19 '09 at 10:26
4

Add a column to your table that will hold a UNIX timestamp.

Once a day or any timeframe which works for you, you run a query that updates that column.

In this case your query should run at midnight and look something like this.

UPDATE table SET rand_id = (UNIX_TIMESTAMP() + (RAND() * 86400));

Then to retrieve the rows, you use a query similar to this.

SELECT * FROM table WHERE rand_id > UNIX_TIMESTAMP() ORDER BY rand_id ASC LIMIT 20

Using a column with a pre-determined random value saves you needing to run a randomization function for every single row in the table for every single request to the page.

joebert
  • 2,595
  • 1
  • 16
  • 22
0

Another possibility is to generate a random permutation, store this in a session (or just the part you need, the algorithm is easily adaptable), and fetch the questions when you need them.

Martijn
  • 4,925
  • 4
  • 33
  • 50
0

To select a random number of rows with possible duplicates

Select Cus_Id from Customer
Where Cus_Id in (
SELECT ABS(CHECKSUM(NewId())) % (select COUNT(*) from Customer) - 0 AS Random FROM Customer)

If you want to start from certain point (id 1500 and above to narrower range 

Select Cus_Id from Customer
Where Cus_Id in (
SELECT ABS(CHECKSUM(NewId())) % (select COUNT(*)+1500 from Customer) - 1300 AS Random FROM Customer)

If you want less you can add SELECT TOP x or TOP x PERCENT

mr R
  • 747
  • 2
  • 8
  • 21