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