问题描述:

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 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 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.

相关阅读:
Top