CODEWITHSUNDEEP
mysql
Starkers
Starkers

Reputation: 10541

Please explain this mysql query.

SELECT * FROM dogs order by rand(dayofyear(CURRENT_DATE)) LIMIT 1

It seems to me that it orders a database by a random number, and this number changes every day. This is a guess, as it'll take me a day to find out if this is true!

How can I change this query to order a database by a new random number every minute rather than every day? I tried this:

SELECT * FROM dogs order by rand(minuteofhour(CURRENT_DATE)) LIMIT 1

but it didn't work :(

Thanks for your time!

Upvotes: 3

Views: 89

Answers (3)

Denis O.
Denis O.

Reputation: 1850

Use combo of MySQL's funcs MINUTE() and NOW(). NOW will return current date, and MINUTE extracts minute value from it.

Upvotes: 0

user650749
user650749

Reputation: 182

I am not good at mysql. but are you sure is there a function minuteofhour in mysql?

The idea of query is to pick a random record from database.

you can do this by: SELECT * FROM dogs order by rand(20) LIMIT 1

it will order by column "a random number from 1-20"

Upvotes: 0

Tom van der Woerdt
Tom van der Woerdt

Reputation: 29965

A random number generator (RNG) usually needs a 'seed value', a value that is used to generate random numbers. If the seed value is always the same, the sequence of random numbers is always the same. This explains why it changes every day.

The easiest way to solve your problem (change it to every minute) is to find a seed value that changes every minute. A good one would be ROUND(UNIX_TIMESTAMP()/60).

SELECT * FROM dogs order by rand(ROUND(UNIX_TIMESTAMP()/60)) LIMIT 1

Upvotes: 4

Related Questions