Is it possible this? I have the following table:

and I'm trying to get the higher col3 value filtering by col1 and col2 my attempt right now is:

SELECT MAX(col3), col2, col1, count(col1) FROM `mytable` WHERE (col1 IN ('code1', 'code2') ) AND ((col2 = 'US') OR (col2 = '0')) GROUP BY col1;

and my result:

But what I need is if both codes have a col2 with value equal to 'US' then return the higher col3 value from those and ignore the ones with '0' or if one of those codes only have one row with col2 equal to '0' then compare between the code1 = 'US' and the code2 = '0' giving the result:

Is it possible to achieve this with a mysql query? or should I use php?

Thank you in advance.

CodePudding user response：

You have very specific where clause, so you need to repeat them in teh subselect as in the Main Select

SELECT * FROM tab1 WHERE col3 = 
(SELECT MAX(col3) FROM tab1  WHERE (col1 IN ('code1', 'code2') ) AND ((col2 = 'US') OR (col2 = '0')))
AND (col1 IN ('code1', 'code2') ) AND ((col2 = 'US') OR (col2 = '0'))
ORDER BY col1
LIMIT 1

Col1  | Col2 | col3
:---- | :--- | ---:
code2 | 0    |   11

db<>fiddle here