CODEWITHSUNDEEP
mysql
user11910577
user11910577

Reputation:

Unexpected behavior of HAVING clause

I have the following table in MySQL-

runs overs
 5     6
 6     7

I am trying to test the behavior of having clause without group by clause.

When i write-
select * from cricket having runs=MIN(runs);
OUTPUT-
runs overs
 5     6

select * from cricket having runs=MAX(runs);
OUTPUT-
Empty Set

What is the reason behind this behavior?

EDIT: On reversing the row order-

runs overs
 6     7
 5     6

The output is as follows-

select * from cricket having runs=MIN(runs);
OUTPUT-
Empty Set

select * from cricket having runs=MAX(runs);
OUTPUT-
runs overs
 6     7

Upvotes: 1

Views: 42

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

You are using select, aggregation function and having clause in improper way ..

could you are using a mysql version prrevious 5.7 so the behaviour is allowed but with unpredictble result .. (for mysql version > 5.6 by defualt this kind on query produce an error)

The preoper way for using aggreagtion function, having a and select columns is based on

the mention in group by clause of the columns not involved in aggregation function

so you first query should be

select * 
    from cricket 
    having runs = ( 
        select min(runs) from cricket
    )

or

select c.* 
from cricket c
inner join  (
    select overs, min(runs) min_runs
    from cricket 
    group by  overs
    order by min_runs desc limit 1
) t on t.overs = c.overs 
    and t.min_runs = c.runs

Upvotes: 1

Related Questions