Re: Sorting in numerical order, and then randomly

Hi Peter, Thanks for the suggestion, suggestion 3 works perfectly if I also switch the order of the numbering as you suggest, because then I can do order by priority desc, rand(), which returns the numbers first and then the 0's, which is what I really wanted. Also, good point about adding higher priority advertisers - I hadn't thought of that. Thanks again, -Lisi At 02:54 AM 11/18/02 -0500, Peter Beckman wrote:

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.

Peter On Sun, 17 Nov 2002, Lisi wrote: I am using MySQL to store ad information in the following table: CREATE TABLE IF NOT EXISTS ads_value (

    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,
    PRIMARY KEY (ID)

) TYPE=MyISAM; 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 (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php ---------------------------------------------------------------------------

Peter Beckman            Systems Engineer, Fairfax Cable Access Corporation
beckman@purplecow.com                             http://www.purplecow.com/

---------------------------------------------------------------------------