Previously I've used ORDER BY RAND(), but now I've had some requests for sponsored results. I could use the ORDER BY SponsoredListing DESC, which will give me ranked results, but I'd like to order by random the remaining results a different way each time, as RAND() does. I tried

ORDER BY SponsoredListing DESC, (RAND()*physician.physician_ID)

which is close, giving me the sponsored, then the same fixed random result. but is there a way to get a different set of random results each time as part of an ORDER BY hierarchy? all help appreciated.

This isn't really random as the physician with the higher id will appear much more often than one with a lower id i.e. take 2 physicians one with an id of 1000 and the other with an id of 1 then rand() * 1000 is likely to be much higher than rand() * 1. Perhaps it should be

This would still always place the higher sponsored items above the lower sponsored items no matter what. If you wanted some lower sponsored items to occasionally appear then you could have a number associated with each sponsorship level i.e. 5-1 where 5 is the highest. Then you could just have :

which would put the higher sponsored items higher in the list but still give a chance to the lower sponsored items to appear.

Mike

Odd, I tried the ORDER by Sponsored listing desc, rand() but couldn't get it to work; probably had some mistake in syntax. It works fine now. Thanks for the suggestions.