# Least case Stament with When then Result

**URL:** <https://www.phphelp.com/t/least-case-stament-with-when-then-result/33687>\
**Category:** MySQL database\
**Created:** [July 7, 2021, 4:03pm UTC](https://www.phphelp.com/t/least-case-stament-with-when-then-result/33687 "2021-07-07T16:03:46Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Book](https://www.phphelp.com/letter_avatar_proxy/v4/letter/b/fbc32d/32.png) [@Book](https://www.phphelp.com/u/Book)\
**Post date:** [July 7, 2021, 4:03pm UTC](https://www.phphelp.com/t/least-case-stament-with-when-then-result/33687/1 "2021-07-07T16:03:46Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ErnieAlex](https://www.phphelp.com/letter_avatar_proxy/v4/letter/e/bc8723/32.png) [@ErnieAlex](https://www.phphelp.com/u/ErnieAlex)\
**Post date:** [July 7, 2021, 4:11pm UTC](https://www.phphelp.com/t/least-case-stament-with-when-then-result/33687/2 "2021-07-07T16:11:27Z")

</div>

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…

---

<div class="post-metadata">

**Author:** ![Book](https://www.phphelp.com/letter_avatar_proxy/v4/letter/b/fbc32d/32.png) [@Book](https://www.phphelp.com/u/Book)\
**Post date:** [July 7, 2021, 4:32pm UTC](https://www.phphelp.com/t/least-case-stament-with-when-then-result/33687/3 "2021-07-07T16:32:06Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ErnieAlex](https://www.phphelp.com/letter_avatar_proxy/v4/letter/e/bc8723/32.png) [@ErnieAlex](https://www.phphelp.com/u/ErnieAlex)\
**Post date:** [July 7, 2021, 5:40pm UTC](https://www.phphelp.com/t/least-case-stament-with-when-then-result/33687/4 "2021-07-07T17:40:41Z")

</div>

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

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

```

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![ErnieAlex](https://www.phphelp.com/letter_avatar_proxy/v4/letter/e/bc8723/32.png) [@ErnieAlex](https://www.phphelp.com/u/ErnieAlex)\
**Post date:** [July 7, 2021, 5:42pm UTC](https://www.phphelp.com/t/least-case-stament-with-when-then-result/33687/5 "2021-07-07T17:42:27Z")

</div>

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.