I need to query my database and partially order and randomize the results.
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 Product #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 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.