CODEWITHSUNDEEP
sqlmysql
nhaa123
nhaa123

Reputation: 9798

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

Upvotes: 2

Views: 98

Answers (3)

Chase
Chase

Reputation: 69201

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.

Upvotes: -1

ajreal
ajreal

Reputation: 47321

SELECT * FROM products ORDER BY views DESC, RAND() LIMIT 5;

maybe ... you can retrieve the records first then sort randomly using other programming language

Upvotes: 2

Darren Kopp
Darren Kopp

Reputation: 77637

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.

Upvotes: 0

Related Questions