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;

Relational Algebra Expression:

Relational Algebra Expression: Max() function with Group by.

Relational Algebra Tree:

Relational Algebra Tree: Max() function with Group by.

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;

Relational Algebra Expression:

Relational Algebra Expression: SQL max() with group by and order by.

Relational Algebra Tree:

Relational Algebra Tree: SQL max() with group by and order by.

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;

Relational Algebra Expression:

Relational Algebra Expression: SQL max() with group by on two columns.

Relational Algebra Tree:

Relational Algebra Tree: SQL max() with group by on two columns.

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.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

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



Follow us on Facebook and Twitter for latest update.