(SQL) Order By Two Columns as One

[MattC]

I need to pull prices from two columns and order them as if they where one.

Example Data.

tbl_products As in DB:
ID|Name|Price|Sale_Price
1|Prod1|£10.00|£0.00
2|Prod2|£20.00|£5.00
3|Prod3|£30.00|£25.00
4|Prod4|£40.00|£0.00
5|Prod5|£50.00|£35.00

Needs to be ordered as:
ID|Name|Price|Sale_Price
2|Prod2|£20.00|£5.00
1|Prod1|£10.00|£0.00
3|Prod3|£30.00|£25.00
5|Prod5|£50.00|£35.00
4|Prod4|£40.00|£0.00

So basically it needs to show the lowest price regardless of whether the lowest is in the column "Price" or "Sale Price". Generally if a product is not sold at sale price then it will have a sale price of £0.00, at this stage I can not change this.

[JasonD]

ORDER BY IF(Sale_Price > 0 AND Sale_Price < Price, Sale_Price, Price)

[MattC]

Thanks Jason but I get a syntax error by the keyword IF

M$ SQL Server

Actual SQL Statement

SELECT     name, price, saleprice
FROM         products
WHERE     catcode = 8
ORDER BY IF (saleprice > 0 AND saleprice < price, saleprice, price)

[MattC]

Right, I've got close to sorting this.

SELECT products.productid, products.Price,
ProdPrice = CASE products.saleprice 
when 0 THEN products.price
else products.saleprice
end, products.saleprice
FROM products
where catcode=61
order by prodprice desc

Works fine apart from when I try to use CASE within the sproc to allow for choice of ORDER BY

order by
CASE WHEN @orderb = '0' THEN productid
END desc,
CASE WHEN @orderb = '1' THEN productid
END asc,
CASE WHEN @orderb = '2' THEN ProdPrice
END asc,
CASE WHEN @orderb = '3' THEN name
END asc,
CASE WHEN @orderb = '4' THEN ProdPrice
END desc,
CASE WHEN @orderb = '5' THEN name
END desc

 
It returns an error stating that ProdPrice is an invalid column. It works fine with price or saleprice so I can only guess it has something to do with the fact that the ProdPrice column doesn't exsist in the DB and is created in the SQL statement/sproc.

Scratching my head a little on this one.

tia

Matt

[MattC]

For anybody that's interested I resolved this via

order by coalesce(nullif(SalePrice,0.00),Price) desc