MySQL, ORDER BY Multiple, 2nd ARG is ignored.

[angelopc]

I'm trying to retrieve some results from a table. I want the results chosen randomly, then sorted by the number of "hits".

Here's the code I'm using:

<?php $latestQuery = "SELECT * FROM pages ORDER BY RAND(), hits DESC LIMIT 100"; ?>

With this syntax, the results are chosen randomly, but they are not sorted by "hits".

You can see the results at Insoogle.com (scroll to latest searches).

I'm sure there's a simple fix for the code I'm using. I just can't seem to figure it out.

[Atli]

Hey.

The problem is that the ORDER BY RAND(), which you use to fetch 100 random rows, is also the primary sort method for those 100 records. The secondary sort column (hits) is never used unless there happen to be two identical rows next to each other in the random list.

What you need to do is first retrieve the 100 records, and then sort them.
Something like:

SELECT * FROM (
    SELECT * 
    FROM pages 
    ORDER BY RAND()
    LIMIT 100
) AS `random_pages`
ORDER BY hits DESC 

I'm betting there is a more optimized way to do this, but I'm to tired to see it at the moment xD

[angelopc]

Bingo! Thank you, Atli. I never would have known to do that. Worked like a charm!