...

View Full Version : order by if condition



hessodreamy
07-19-2006, 03:38 PM
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.


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

Fumigator
07-19-2006, 04:10 PM
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.

hessodreamy
07-19-2006, 04:14 PM
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...

Beagle
07-19-2006, 04:19 PM
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?

hessodreamy
07-19-2006, 05:01 PM
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.

Kid Charming
07-19-2006, 11:53 PM
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?

hessodreamy
07-20-2006, 10:00 AM
yeah, first row to be (randomly) row a or b. The other rows to be random.

guelphdad
07-20-2006, 03:50 PM
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.



(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)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum