MySQL MAX() function with group by
MAX() function with group by
MySQL MAX() function with GROUP BY retrieves maximum value of an expression which has undergone a grouping operation (usually based upon one column or a list of comma-separated columns).
Example:
Sample table: book_mast
Code:
SELECT cate_id, MAX( book_price)
FROM book_mast
GROUP BY cate_id;
Relational Algebra Expression:

Relational Algebra Tree:

Explanation:
The above MySQL statement will extract all “cate_id”s and the maximum 'book_price' in each group of 'cate_id'. ‘GROUP BY ‘ clause have grouped “cate_id's”.
Output:
mysql> SELECT cate_id, MAX(book_price) -> FROM book_mast -> GROUP BY cate_id; +---------+-----------------+ | cate_id | MAX(book_price) | +---------+-----------------+ | CA001 | 145.00 | | CA002 | 250.00 | | CA003 | 200.00 | | CA004 | 100.00 | | CA005 | 180.00 | +---------+-----------------+ 5 rows in set (0.02 sec)
Pictorial Presentation:

MySQL MAX() function with group by on two columns
Sample table: publisher
Code:
SELECT country,pub_city,MAX(no_of_branch)
FROM publisher
GROUP BY country,pub_city;
Relational Algebra Expression:

Relational Algebra Tree:

Explanation:
The above MySQL statement will extract those countries ('country') and publisher cities ('pub_city') which has the maximum number of branches ('no_of_branch') in each group of 'country' and 'pub_city'.
Output:
mysql> SELECT country,pub_city,MAX(no_of_branch) -> FROM publisher -> GROUP BY country,pub_city; +-----------+-----------+-------------------+ | country | pub_city | MAX(no_of_branch) | +-----------+-----------+-------------------+ | Australia | Adelaide | 6 | | India | Mumbai | 10 | | India | New Delhi | 10 | | UK | Cambridge | 6 | | UK | London | 8 | | USA | Houstan | 25 | | USA | New York | 15 | +-----------+-----------+-------------------+ 7 rows in set (0.02 sec)
MySQL MAX with group by and order by
Code:
SELECT country,pub_city,MAX(no_of_branch)
FROM publisher
GROUP BY country,pub_city
ORDER BY country;
Relational Algebra Expression:

Relational Algebra Tree:

Explanation:
The above MySQL statement will extract those countries ('country') and publisher cities ('pub_city') which have the maximum number of branches ('no_of_branch') for each group of 'country' and 'pub_city'. ‘GROUP BY ‘ clause have grouped 'country' and 'pub_city' . The 'country' column have sorted in ascending order by the usage of ORDER BY clause.
Output:
mysql> SELECT country,pub_city,MAX(no_of_branch) -> FROM publisher -> GROUP BY country,pub_city -> ORDER BY country; +-----------+-----------+-------------------+ | country | pub_city | MAX(no_of_branch) | +-----------+-----------+-------------------+ | Australia | Adelaide | 6 | | India | Mumbai | 10 | | India | New Delhi | 10 | | UK | Cambridge | 6 | | UK | London | 8 | | USA | Houstan | 25 | | USA | New York | 15 | +-----------+-----------+-------------------+ 7 rows in set (0.00 sec)
MySQL MAX() function with distinct
MAX() function with distinct
MySQL MAX() function retrieves the maximum value of an expression if the function is accompanied by a DISTINCT clause.
Example:
Sample table: book_mast
Code:
SELECT cate_id,MAX(DISTINCT no_page)
FROM book_mast
GROUP BY cate_id;
Relational Algebra Expression:

Relational Algebra Tree:

Explanation:
The above MySQL statement will extract category ('cat_id') wise maximum number of pages ('no_page') from the 'book_mast' table.
Output:
mysql> SELECT cate_id,MAX(DISTINCT no_page) -> FROM book_mast -> GROUP BY cate_id; +---------+-----------------------+ | cate_id | MAX(DISTINCT no_page) | +---------+-----------------------+ | CA001 | 345 | | CA002 | 600 | | CA003 | 510 | | CA004 | 350 | | CA005 | 350 | +---------+-----------------------+ 5 rows in set (0.00 sec)
Previous:
Max()
Next:
Max() with having
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join