[RESOLVED] Problem ordering results by 2 fields

asa_carter

I have two price fields in my DB: regprice and offerprice.

regprice must have a value but offerprice is optional

I.e.

regprice = 30
offerprice = 25

I have problem sorting my results by price order because if I sort by regprice it may have a higher value than the price it is being sold at and return an inaccurate order.

Likewise if I sort by offerprice it will only sort the order of items that have an offerprice.

Help?

Lars_Berg

order by coalesce(offerprice,regprice)

asa_carter

It doesn't seem to work?

http://www.argyllnewmedia.co.uk/itrenz/list.php?id=01000000

jib0

Try this:

SELECT IFNULL(offerprice, regprice) AS price FROM Products ORDER BY price ASC;

Thi will work only if "offerprice" is set to NULL when there is no offerprice.

asa_carter

I have changed all of the offerprice fields from '0' to 'NULL' where there is no value.

It seems to group the items that have a NULL offerprice at the front, then orders the rest by regprice.

SELECT IFNULL(offerprice, regprice) AS price FROM item ORDER BY price ASC

http://www.argyllnewmedia.co.uk/itrenz/list.php?id=01000000

asa_carter

ah sorry...

I was accidentally updating the production server rather than the test server.