Jump to content

Problem With Null Values Using Least Statement


Manny
 Share

Recommended Posts

I have a shop on my website which uses prices from various online merchants and I want to use a query which will display the lowest price for each individual product.The columns are as follows:`ID`, `Name`, `Kitbag Price`, `Subside Sports Price`, `Sports Direct Price`, `Amazon Price`, `Asda Price`, `Play Price`Not all products are available from all six merchants, so there are some NULL values in pretty much every row in the table.All the price fields are of 'Decimal' type with a 4,2 setting, with PHP code converting them to £ (GBP) in the front end.Below are 3 rows of example data.

`ID`, `Name`	, `Kitbag Price`, `Subside Sports Price`, `Sports Direct Price`, `Amazon Price`, `Asda Price`, `Play Price``1` , `Product 1`, `9.99`, `12.99`, `NULL`, `15.99`, `NULL`, `NULL``2` , `Product 2`, `NULL`, `NULL`, `13.99`, `NULL`, `12.50`, `12.99``3` , `Product 3`, `12.99`, `11.99`, `11.99`, `NULL`, `NULL`, `9.99`

Below is my query which should get the lowest value in that row, but because of the NULL values, it is coming up with a NULL result.

SELECT *, LEAST(`Kitbag Price`, `Subside Sports Price`, `Sports Direct Price`, `Amazon Price`, `Asda Price`, `Play Price`) FROM `products` ORDER BY `products`.`ID` ASC

I have also tried changing the price columns from 'Decimal' to 'VARCHAR', leaving the NULL fields empty, but using row 1 as an example, the result would display as 12.99 because of the leading '1' character, when the actual result should display as 9.99.Sorry for the long post but I hope this explains everything and gives somebody the chance to help me solve the problem!

Link to comment
Share on other sites

Well, it's not pretty but you could try wrapping the arguments in LEAST with IFNULL, e.g.

SELECT *, LEAST( IFNULL(`Kitbag Price`, 99.99), IFNULL(`Subside Sports Price`, 99.99), .....

That way null gets conveted to 99.99 in the LEAST calculation. Not great though.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...