Ordering and randomizing a query at the same time
I need to query my database and partially order and randomize the results. For instance,
SELECT * FROM products ORDER BY views DESC LIMIT 5
would result something like this:
id name views --------------------- 0 Product #1 2 1 Product #2 1 2 Pro开发者_如何学JAVAduct #3 0 3 Product #4 0 4 Product #5 0
Now, how can I add RAND() (or something similar) in the query so that the last three items (Products #3, #4 and #5) would appear in random order since they've got the same amount of views, but the first two still descending based on the same views-field?
I tried something like
SELECT * FROM products ORDER BY views, RAND() DESC LIMIT 5
but obviously it doesn't work.
Thank you in advance.
SELECT * FROM products ORDER BY views DESC, RAND() LIMIT 5;
maybe ... you can retrieve the records first then sort randomly using other programming language
i don't know about in mysql, but in sql server you can do order by newid()
. i'm sure doing the same thing in mysql would work also, i just don't know what the function in mysql is to generate a guid.
Does mysql support union
and union all
? I have to imagine it does...
If so, what about something like:
SELECT * FROM products where views > 0 ORDER BY views DESC
union all
SELECT * FROM products where views = 0 order by RAND()
Obviously your full query may be more complicated and you may need to modify the where clauses to fit your need, but seems like a fairly flexible solution.
精彩评论