MySQL MAX() function with having

MAX() function with having

MySQL MAX() function retrieves the maximum value from an expression which has undergone a grouping operation by GROUP BY clause and filtered using HAVING clause followed by some condition.

Example:

Sample table: publisher

Code:

SELECT country,MAX(no_of_branch)
FROM publisher
GROUP BY country
HAVING MAX(no_of_branch)>=8;

Relational Algebra Expression:

Relational Algebra Tree:

Explanation:

The above MySQL statement will extract those countries ('country') which have eight or more branches.

Output:

mysql> SELECT country,MAX(no_of_branch)
    -> FROM publisher
    -> GROUP BY country
    -> HAVING MAX(no_of_branch)>=8;
+---------+-------------------+
| country | MAX(no_of_branch) |
+---------+-------------------+
| India   |                10 | 
| UK      |                 8 | 
| USA     |                25 | 
+---------+-------------------+
3 rows in set (0.00 sec)

Previous: Max() with group by
Next: MIN()