Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2007
    Posts
    34

    Unanswered: order by . . . random and not random in same statement

    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.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Code:
    ORDER BY SponsoredListing DESC, (RAND()*physician.physician_ID)
    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

    Code:
    ORDER BY SponsoredListing DESC, RAND()
    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 :

    Code:
    ORDER BY SponsorLevel * Rand() desc
    which would put the higher sponsored items higher in the list but still give a chance to the lower sponsored items to appear.

    Mike

  3. #3
    Join Date
    Feb 2007
    Posts
    34
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •