Mysql : random sort than sort by a specific column

Question

I have a database with rated items in it. When I want to display all the items from a specific category and sort the results by rate than by number of likes, it's easy and it's working:

$query =  "SELECT * FROM infos WHERE category = '".$categories."'";
$query .= "ORDER BY `rate` DESC, `like` DESC";

The problem is when the results have the same value, they show up in alphabetical order. So, I would like to randomize the database before I sort it by rate and like. I just want to let the same chance to all items and those that have the same value not to be advantaged by alphabetical order.

I tried this, but it didn't work:

$query =  "SELECT * FROM infos WHERE category = '".$categories."'";
$query .= "ORDER BY RAND(), ORDER BY `rate` DESC, `like` DESC";

Can somebody help me? I'm stuck.

@zeantsoi It results in a MySQL syntax error - that should have been caught and reported. — user2246674
– user2246674, Commented Jun 15, 2013 at 20:37
Make sure to 1) test queries on the CLI or "SQL Studio"; 2) correctly handle errors in SQL queries from PHP; and 3) use placeholders/prepared statements. — user2246674
– user2246674, Commented Jun 15, 2013 at 20:38

Ruben · Accepted Answer · 2013-06-15 20:35:15Z

4

You should turn it around:

SELECT * FROM infos WHERE category=...
ORDER BY rate DESC, like DESC, RAND();

That way it sorts by rate, then like and finally random if rate and like are equal.

Also your original ORDER BY with RAND() doesn't work because you use two ORDER BY clauses.

answered Jun 15, 2013 at 20:35

Ruben

5,0952 gold badges27 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pmrotule Over a year ago

Thanks, it's working! First, I tried to remove one of the ORDER BY, but it randomized all my results. If I put the RAND() at the end of my query, it's working like a charm!

Collectives™ on Stack Overflow

Mysql : random sort than sort by a specific column

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related