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:

-- This SQL query retrieves the maximum number of branches for each country, filtering the results to include only countries with maximum branches greater than or equal to 8.
SELECT country, MAX(no_of_branch) -- Selects the country and calculates the maximum number of branches for each country
FROM publisher -- Specifies the table from which to retrieve data (publisher table)
GROUP BY country -- Groups the results by country, so that the maximum number of branches is calculated for each country separately
HAVING MAX(no_of_branch) >= 8; -- Filters the results to include only countries where the maximum number of branches is greater than or equal to 8

Explanation:

• This SQL query retrieves data from the publisher table.


• It calculates the maximum number of branches for each country.


• The GROUP BY clause ensures that the results are grouped by country, allowing for the maximum number of branches to be calculated separately for each country.


• The HAVING clause filters the results to include only countries where the maximum number of branches is greater than or equal to 8.


• Here's how the process works:


• The query selects the country and calculates the maximum number of branches for each country.


• It groups the results by country using GROUP BY country.


• The HAVING clause then filters the results to include only countries where the maximum number of branches is greater than or equal to 8.


• Finally, the query returns the country and the maximum number of branches for each country, but only for countries with maximum branches greater than or equal to 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()