w3resource

SQL MAX() with group by

Max() function with Group by

In this page we are discussing, how the GROUP BY clause along with the SQL MAX() can be used to find the maximum value of a column over each group.

Example:

Sample table: agents


To get data of 'working_area' and maximum 'commission' for the agents of each 'working_area' from the 'agents' table with the following condition -

1. the 'working_area' should come in a group,

the following SQL statement can be used:

SELECT working_area, MAX(commission) 
FROM agents 
GROUP BY working_area;

Output:

WORKING_AREA                        MAX(COMMISSION)
----------------------------------- ---------------
San Jose                                        .12
Torento                                         .15
London                                          .15
Hampshair                                       .11
New York                                        .12
Brisban                                         .13
Bangalore                                       .15
Chennai                                         .14
Mumbai                                          .11 

SQL max() with group by and order by

To get data of 'cust_city', 'cust_country' and maximum 'outstanding_amt' from the customer table with the following conditions -

1. the combination of 'cust_country' and 'cust_city' should make a group,

2. the group should be arranged in alphabetical order,

the following SQL statement can be used:

SELECT cust_city, cust_country, 
MAX(outstanding_amt) 
FROM customer 
GROUP BY cust_country, cust_city 
ORDER BY cust_city;

Sample table: customer


Output:

CUST_CITY                           CUST_COUNTRY         MAX(OUTSTANDING_AMT)
----------------------------------- -------------------- --------------------
Bangalore                           India                               12000
Brisban                             Australia                            7000
Chennai                             India                               11000
Hampshair                           UK                                   5000
London                              UK                                  11000
Mumbai                              India                               12000
New York                            USA                                  6000
San Jose                            USA                                  3000
Torento                             Canada                              11000

Pictorial Presentation:

SQL MAX Function with GROUP BY ORDER BY Example

SQL max() with group by on two columns

To get data of 'cust_city', 'cust_country' and maximum 'outstanding_amt' from the 'customer' table with the following condition -

1. the combination of 'cust_country' and 'cust_city' column should make a group,

the following SQL statement can be used :

SELECT cust_city, cust_country,
MAX( outstanding_amt )
FROM customer
GROUP BY cust_country, cust_city;

Sample table: customer


Output:

CUST_CITY                           CUST_COUNTRY         MAX(OUTSTANDING_AMT)
----------------------------------- -------------------- --------------------
Bangalore                           India                               12000
Brisban                             Australia                            7000
Chennai                             India                               11000
Hampshair                           UK                                   5000
London                              UK                                  11000
Mumbai                              India                               12000
New York                            USA                                  6000
San Jose                            USA                                  3000
Torento                             Canada                              11000

Note: Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition

Here is a slide presentation of all aggregate functions.

Practice SQL Exercises

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

Previous: Max Function
Next: Max Having, Where, in