Your options, as I see them, with 3 being the best I could come up with:

    1. Make two queries.  Depending on how many rows returned, this may be
       the less taxing option processor wise.
    2. Make the random query.  As you iterate through them push folks with
       priority 0 on one stack, priority 1 another stack, etc.  At the end
       of it all you'll have a bunch of stacks with a randomly ordered list
       of advertisers.
    3. (This is the obvious winner)
       select * from ads_value where status='current' order by priority asc, rand()

       This will return 0's first, 1,2,3... etc after that in a random
       order.  I might recommend re-ordering your "priority" making 100 the
       highest and 1 the lowest priority above 0.  This way if you get an
       advertiser that beats everyone out, they can be 101 or 150 or (if
       you are lucky in this market) 2000.  If you do it your way (as I
       understand it) you will have to bump down #1 to #2, #2 to #3, etc in
       order to put a newer higher priority advertiser first.


On Sun, 17 Nov 2002, Lisi wrote:

> I am using MySQL to store ad information in the following table:
>     img_link varchar(50),
>     text text,
>     service varchar(50) default NULL,
>     title varchar(50) default NULL,
>     priority int(2) default '0',
>     status enum('current', 'old'),
>     ID int(3) NOT NULL auto_increment,
> Ads for which advertisers pay more will have a higher priority - i.e. 1, 2,
> 3, etc. Everything else will have a priority of 0.  When the page loads, I
> want to first display any ads that have a priority higher than 0 to be
> displayed in order, and then the remaining ads with a priority if 0 to be
> displayed in random order. They have to be displayed in a different order
> each time, so that each ad has the same chance of being displayed in a
> particular spot as any other ad. The only spots a random ad cannot be in is
> one taken by a higher paying ad. I hope this is clear.
> Is it possible to do this with one query? Or would I have to use 2
> different queries:
> select * from ads_value where status = 'current' and priority > 0 order by
> priority asc
> and then
> select * from ads_value where status = 'current' and priority = 0 order by
> RAND()
> TIA,
> -Lisi
> --
> PHP Database Mailing List (
> To unsubscribe, visit:

Peter Beckman            Systems Engineer, Fairfax Cable Access Corporation
[EMAIL PROTECTED]                   

PHP Database Mailing List (
To unsubscribe, visit:

Reply via email to