Take the 2-minute tour ×
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. It's 100% free, no registration required.

My fiddle is here.

Work around : My table structure is

create table test (grIds int(1), amount int(1));
insert into test values
(1,10),
(1,20),
(1,15),
(2,0),
(3,8),
(3,25),
(3,16),
(4,0),
(4,21);

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
add comment

2 Answers

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.)

Remarks

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

share|improve this answer
3  
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
1  
@Mat that should be an answer. –  ypercube Jul 19 '13 at 9:06
add comment

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 
GROUP BY grIds 
ORDER BY maxa DESC 
LIMIT 1;

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
add comment

Your Answer

 
discard

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.