[SOLVED] Finding larger of 2 columns?

[triphis]

Hi there everyone

 

Simple featured item script for an online store... Items have 2 columns, RETAIL and SALE (price, that is).

 

There is the option for the store owner to have the featured item to be the most expensive one... and I originally wrote the query to select the largest by RETAIL. However, obviously if that item is on sale for much less, it might not be the most expensive...

 

So, what I need is this:

 

SELECT id, LARGER(RETAIL,SALE) as highest FROM products ORDER BY highest DESC LIMIT 1

 

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

 

Is there a function to choose the larger of two as I have done above???

Thanks!!!

[bubblegum.anarchy]

I am a little confused... is the SALE price ever going to be greater than the RETAIL price?

[triphis]

Nope

 

I see my mistake.

 

Okay, let me try again: The items will not always be on sale (SALE = 0). However, if they are, then the SALE price is the price that the customer would pay, not the RETAIL price. So just ordering by RETAIL price would not be appropriate.

 

I am looking for a way to rank all by the price that *would* be paid by the customer, to find the actual most expensive item.

 

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

 

Scenario:

 

Item 1 - retail=$300 / sale=$200

Item 2 - retail=$250 / sale=$0

 

I want my query to find Item 2 to be the most expensive

 

I hope that clears things up, and I hope you can still help me ^^

[bubblegum.anarchy]

Then set SALE to null and use:

 

SELECT ifnull(SALE, RETAIL)

 

The above will return the SALE value if SALE IS NOT NULL otherwise the RETAIL value will be returned.

 

 

[triphis]

Awesome! thank you very much

[bubblegum.anarchy]

You can also use the following (if SALE must be zero instead of null)

 

SELECT if (SALE = 0, RETAIL, SALE)