CODEWITHSUNDEEP
sqlmysql
user156073
user156073

Reputation: 2001

Selecting Random Rows in MySQL

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?

Upvotes: 14

Views: 21787

Answers (4)

mr R
mr R

Reputation: 1126

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

Upvotes: 0

Martijn
Martijn

Reputation: 5653

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.

Upvotes: 0

joebert
joebert

Reputation: 2663

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.

Upvotes: 4

Eric
Eric

Reputation: 95103

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

Upvotes: 37

Related Questions