Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New Coder
    Join Date
    May 2005
    Location
    Leeds, UK
    Posts
    83
    Thanks
    1
    Thanked 0 Times in 0 Posts

    order by if condition

    I'm trying to return a list of products, where the top of the list will be one of 2 products, the choice being randomly selected. I tried a rand() condition in the ORDER BY clause, which partly works, but of course the rand() function is evaluated for every row, so it doesn't give the consistent results i need.

    Any Ideas? using 4.1 by the way.
    Code:
    SELECT productID, prodName, adMessage, nPrice, nPrice*(1+vatRate) as salePrice, showSale, originalPrice, pic.picType, pic.ordering
    			FROM tProducts p 
    			JOIN pictures pic ON p.productID=pic.relProdId and pic.picExists = 1 
    			JOIN taxCodes tax ON p.taxCode=tax.taxCode
    			WHERE p.featuredProduct=1
    			AND p.stocklevel>-3
    			GROUP BY p.productID
    			ORDER BY productID=if(rand()<0.5,117029,117035 ) desc, rand()
    			LIMIT 10
    Last edited by hessodreamy; 07-19-2006 at 02:43 PM.

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    If I understand you right, you want to randomize the order of the product_id prior to selecting the data from your table. So I would suggest adding an expression (or second query) before you hit the current query that determines which product_id to sort by.

  • #3
    New Coder
    Join Date
    May 2005
    Location
    Leeds, UK
    Posts
    83
    Thanks
    1
    Thanked 0 Times in 0 Posts
    yes it would be very easy for me to do the randomisation in php. I'm always trying to do things in just one query...

  • #4
    Senior Coder
    Join Date
    Jul 2005
    Location
    New York, NY
    Posts
    1,084
    Thanks
    4
    Thanked 19 Times in 19 Posts
    Code:
    SELECT productID, prodName, adMessage, nPrice, nPrice*(1+vatRate) as salePrice, showSale, originalPrice, pic.picType, pic.ordering, rand() AS r, CASE WHEN r < 0.5 THEN 117029 ELSE 117035 END AS prodID
    			FROM tProducts p 
    			JOIN pictures pic ON p.productID=pic.relProdId and pic.picExists = 1 
    			JOIN taxCodes tax ON p.taxCode=tax.taxCode
    			WHERE p.featuredProduct=1
    			AND p.stocklevel>-3
    			GROUP BY p.productID
    			ORDER BY productID = prodID desc, r
    			LIMIT 10
    Do this do what you want?

  • #5
    New Coder
    Join Date
    May 2005
    Location
    Leeds, UK
    Posts
    83
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Code:
    Unknown column 'r' in 'field list'
    I tried fiddling with your query. and putting parts of it in subqueries, but i ended up at square one, where the rand() is evaluated for every row, therefore giving mostly random results, instead of having one of 2 at the top.

  • #6
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You want the first row to be either row a or row b, determined randomly. What about the rest of your result set? Should it be random, too, or follow a pattern?

  • #7
    New Coder
    Join Date
    May 2005
    Location
    Leeds, UK
    Posts
    83
    Thanks
    1
    Thanked 0 Times in 0 Posts
    yeah, first row to be (randomly) row a or b. The other rows to be random.

  • #8
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    The GROUP BY clause is unnecessary, you aren't aggregating anything.

    what you will need is a UNION.

    I'm not sure I read your query above correctly, are these the product IDs you want to choose from in the first row? 117029,117035 let's assume they are and you can change what you need.

    Code:
    (select 
    foo,
    bar,
    qux
    from yourtable
    where productid IN (117029,117035)
    order by rand()
    limit 1)
    UNION
    (select
    foo,
    bar,
    qux
    from yourtable
    where productid NOT IN (117029,117035)
    order by rand()
    limit 9)


  •  

    Posting Permissions

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