On Mon, Jul 5, 2010 at 1:09 PM, Mike Little <[email protected]> wrote:
>
> hi guys, slightly off-topic...
>
> my product table contains normalprice and saleprice (not really named this
> but as an example). i wish to give the customer the ability to order by
> lowest to highest price taking into account both the normal and sale price.
>
> not quite sure how to achieve this using an efficient mysql query.
>
I'm no SQL guru (as the SQL gurus that I've worked with will tell you), but
off the top of my head, a CASE statement that compares saleprice and
normalprice, and sets a column based on the lowest one:
SELECT
name,
description,
normalprice,
saleprice,
CASE
WHEN normalprice < saleprice THEN normalprice
ELSE saleprice
END AS orderbyprice
FROM
tablename
ORDER BY
orderbyprice
untested... but something like that?
--
Charlie Griefer
http://charlie.griefer.com/
I have failed as much as I have succeeded. But I love my life. I love my
wife. And I wish you my kind of success.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:335067
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
