Least case Stament with When then Result

Book · July 7, 2021, 4:03pm

i wrote a query to return the max value of multiple columns and then to return the name of the column with the max value, the query seems to be working except for 2 things it should exclude anything where the ‘qty’ column associated with the value column is <1 and it is not, so that cause the second issue where if 2 columns have the same value and they are the highest then it returns the column name that appears first instead of the column name that is associated with the ‘qty’ column >0 here is the query

SELECT order_qty, po, ShipDate, Status,
LEAST( IF (ABCNewPrice > 0 AND ABCQty > 0, ABCNewPrice, 999999),
IF(ANewPrice>0 AND AQty>0 ,ANewPrice,999999),
IF(AUsedPrice>0 AND AUsedQty>0,AUsedPrice,999999))
as theMin,

CASE LEAST(
IF (ABCQty > 0, ABCNewPrice, 999999),
IF(AQty>0 ,ANewPrice,999999),
IF(AUsedQty>0,AUsedPrice,999999))
WHEN ABCNewPrice THEN ‘ABCNewPrice’
WHEN ANewPrice THEN ‘ANewPrice’
WHEN AUsedPrice THEN ‘AUsedPrice’
END AS LeastVarColumn
FROM cabs

ErnieAlex · July 7, 2021, 4:11pm

If you need to select two columns based on a value, most likely you would need two SELECT’s.
One to locate the max value and then another to locate all the columns with that value.

I do not understand why you are using all of the IF’s… Why not just use the MAX() function. That is why that function was created. MAX(ABCNewPrice) would give you the max for that field…

Book · July 7, 2021, 4:32pm

i am not trying to get the max for the entire column, i want the max of all of those columns for each row…
ABCNewPrice, ANewPrice,AUsedPrice ( holds 4,5,6) so it should return 6 which it does, the only thing not working is if its (4,6,6) it should check ABCQty, AQty, AUsedQty and see which is greater than 0 (there will only be 1 of those columns per row that holds a number greater than 0) and then pick that column title–so it would return 6, AUsedPrice for this instance as AusedQty=5.

ErnieAlex · July 7, 2021, 5:40pm

Well, you can get the MAX for those three like this:

SELECT GREATEST(ABCNewPrice, ANewPrice, AUsedPrice) From...

That select would get you the max from those three cols. Then, get them like this:

SELECT IF(ABCNewPrice=GREATEST(ABCNewPrice, ANewPrice, AUsedPrice), "ABCNewPrice"), IF(ANewPrice=GREATEST(ABCNewPrice, ANewPrice, AUsedPrice), "ANewPrice"), IF(AUsedPrice=GREATEST(ABCNewPrice, ANewPrice, AUsedPrice), "AUsedPrice") FROM ...

Something loosely like that should work for you.

ErnieAlex · July 7, 2021, 5:42pm

But, this is a very odd database if you must do that. I suspect that your database was not designed very well. Or, your logic on what you are retrieving is off somehow. You might want to think out your database design further.