Order by something, then random?

Question

I have a query, something like this:

..ORDER BY photo.sort_order DESC, profile.description DESC, RAND()

So this means, records with photos are shown first, then those with descriptions. Within that I want the order to be random (so if there's ten records with photos, they should always be at the top but ordered randomly)

The above doesn't work and I know rand() is not great performance wise. What would be another simple solution?

Answer 1

probably want change your select a bit to take the desc txt out of the equation since you're only interested in whether it's null.

 select photo.sort_order,
        profile.description is not null as desc_order,
        profile.description,
        rand() as r
 from
        photo photo, profile profile
order by 
        photo.sort_order desc, 
        desc_order desc, 
        r