CODEWITHSUNDEEP
mysqlhaving
martinkunev
martinkunev

Reputation: 1405

Odd behavior of max and having in MySQL when max==0

I have the following table:

mysql> select * from foo;
| id | value | bar  |
+----+-------+------+
|  1 |     2 |    3 |
|  2 |     0 |    3 |
|  1 |     1 |    5 |

I want to select the tuple with the maximum value for each id. However, when max(value) is 0, I don't get a result.

mysql> select id,max(value),bar from foo group by id having max(value);
| id | max(value) | bar  |
+----+------------+------+
|  1 |          2 |    3 |

Is this supposed to behave like that and if so, why?

Upvotes: 1

Views: 166

Answers (2)

McNets
McNets

Reputation: 10807

IMHO you can get MAX couple by multiplying (id x value).

create table foo(id int, value int);
insert into foo values
(2,0),
(1,0),
(2,1),
(3,0),
(2,2);

select id, value
from foo
order by (id * value) desc
limit 1;

id | value
 2 |  2

drop table foo;

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

HAVING cannot be used in any way to pick a record out of a group of records as defined by the fields used in the GROUP BY clause. It is rather applied to the group as a whole.

So, in your case, you have to do a self-join to get the rest of the table fields:

select t1.id, t1.value, t1...
from foo as t1
join (
   select id, max(value) as max_value
   from foo 
   group by id
) as t2 on t1.id = t2.id and t1.value = t2.max_value

Upvotes: 1

Related Questions