Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

My fiddle is here.

Work around : My table structure is

create table test (grIds int(1), amount int(1));
insert into test values

I have two select clause

select grIds, max(amount) from test
group by grIds having max(amount);

select grIds, max(amount) from test
group by grIds;

My question is why select with having keeps omitting row having grIds = 2 ?

share|improve this question

HAVING clauses can place extra restrictions on the output rows of an aggregate query – just like WHERE clauses do in record-by-record queries. A HAVING clause can impose conditions on any output column or combination of columns, aggregate or not.

As far as NULL is concerned, the following two facts are worth knowing (and hardly surprising, I would guess):

Rows for which the HAVING condition evaluates to NULL won't be included in the result set. (“Only true is good enough.”)

“HAVING IS [NOT] NULL” is a legal and often useful condition, whether is aggregate or not. (But if is non-aggregate, you may save the engine some work by changing HAVING to WHERE and placing the condition before the “GROUP BY” clause. This goes for any condition on non-aggregate columns.)


MAX ignores any null values. For character columns, MAX finds the highest value in the collating sequence.

share|improve this answer
There are no nulls here, but I believe only the classic "0 evaluates to false" in boolean context. – Mat Jul 19 '13 at 7:27
@Mat that should be an answer. – ypercubeᵀᴹ Jul 19 '13 at 9:06

The other answer and the commenters explained the reason for the having behavior. I was wondering what you actually wanted to do, is it this:

SELECT grIds, MAX(amount) maxa 
FROM test 

Would have left a comment instead - alas, not enough points yet.

share|improve this answer
No. I just need to know why it is happening – Praveen Prasannan Oct 3 '13 at 11:27

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.