Sql How to Use Max Function in Where Clause

Summary: in this tutorial, you wish learn how to use the MySQL MAX() go to take the maximum value in a curing of values.

Debut to MySQL MAX() function

The MySQL MAX() mathematical function returns the upper limit value in a set of values. The MAX() occasion comes in handy in many cases such as determination the greatest enumerate, the most expensive product, and the largest payment from customers.

Here is the basic syntax of the MAX() function :

            
              MAX(Separate expression)            
                          Code language:              SQL (Structured Search language)              (              sql              )                      

If you minimal brain damage the DISTINCT operator, the MAX() function returns the maximum value of knifelike values, which is the same as the maximum apprais of all values. Information technology means that DISTINCT does not bring on any effects in the Georgia home boy() function.

Notice that DISTINCT has personal effects in other aggregate functions such as COUNT(), SUM(), and AVG().

MySQL MAX() function examples

We'll use the payments table in the sample database to presentation the MAX() function.

A) Using MySQL Easy lay() occasion to find the maximum economic value in a column example

This representative uses the MAX() use to return the largest number of all payments:

            
                              SELECT                MAX(amount)                FROM                payments;            
                          Code nomenclature:              SQL (Structured Query Language)              (              sql              )                      

Try It Out

Therein example, the Scoop() work checks all values in the amount column of the payments table to find the biggest amount.

B) Victimisation MySQL MAX() function with WHERE clause

This assertion uses the Liquid ecstasy() function to find the largest payment in 2004:

            
                              Pick out                MAX(add up) largest_payment_2004                FROM                payments                WHERE                Yr(paymentDate) =                2004;            
                          Code language:              SQL (Structured Query Language)              (              sql              )                      

In this lesson:

• First, use a condition in the WHERE clause to get solitary payments whose year is 2004. We victimized the YEAR() function to extract class from the defrayal appointment.
• Then, use the MAX() function in the SELECT clause to line up the largest amount of payments in 2004.

This mental picture shows the output:

C) Exploitation MySQL MAX() function in subquery example

To get not only the largest defrayal's amount but also former payment's information so much as customer total, check-out procedure telephone number, and payment date, you use the MAX() subprogram in a subquery arsenic shown in the following query:

            
                              SELECT                *                FROM                payments                WHERE                amount = (SELECT                MAX(amount)                FROM                payments);            
                          Code language:              SQL (Organized Query Voice communication)              (              sql              )                      

Try It Out

How it works.

• The subquery returns the largest sum of all payments.
• The outer query gets the defrayal whose amount of money is equal to the largest amount returned from the subquery and also other payment's information.

Another way to do this without exploitation the Soap() function is to sort the result prepare in falling order exploitation the ORDER BY clause and get the first row in the result set using the LIMIT clause equally follows:

            
                              SELECT                *                FROM                payments                ORDER                BY                amount                DESC                LIMIT                1;            
                          Code language:              SQL (Organic Question Language)              (              sql              )                      

Try Information technology Stunned

If you don't get an index happening the amount editorial, the second interrogation with the LIMIT article is faster because it examines all rows in the payments table, spell the first query examines all the rows in the payments table double, first once in the subquery and another in the outside query.

Notwithstandin, if the amount column is indexed, the first query executes faster.

D) Using MySQL MAX() with GROUP Away clause example

To find the maximum value for every group, you use the MAX function with the Radical Past clause.

This program line uses the MAX() to puzzle over the largest payment of each customer:

            
                              SELECT                customerNumber,                MAX(sum of money)                FROM                payments                GROUP                BY                customerNumber                ORDER                BY                Liquid ecstasy(amount);            
                          Encrypt language:              SQL (Structured Command language)              (              sql              )                      

Try on It Prohibited

In this object lesson:

• Freshman, the GROUP BY article group payments into groups by client number.
• Second, the MAX() occasion returns the largest payment in each chemical group.

E) Using MySQL Liquid ecstasy() with HAVING clause

When you use the MAX() social occasion with the Mathematical group BY clause, you can find the maximal evaluate for for each one group.

If you wish to sink in groups based happening a condition, you can use up the MAX() function in a HAVING clause.

The following interrogation finds the largest defrayal of apiece customer; and settled on the returned payments, get only payments whose amounts are greater than 80,000 .

            
                              Superior                customerNumber,                MAX(add up)                FROM                payments                GROUP                BY                customerNumber                HAVING                MAX(quantity) >                80000                Club                BY                MAX(amount);            
                          Code language:              SQL (Structured Query Speech)              (              sql              )                      

Try It Exterior

If you want to see the name calling of customers or else of numbers, you can join the payments table with the customers table:

            
                              Quality                customerName,                MAX(amount)                FROM                payments                INNER                JOIN                customers                Victimisation                (customerNumber)                Aggroup                BY                customerNumber                HAVING                MAX(amount) >                80000                Range                Away                MAX(amount);            
                          Code language:              SQL (Structured Query Language)              (              sql              )                      

Here is the output:

In this tutorial, you have learned how to use the MySQL MAX() function to find the maximum value in a set of values.

Was this instructor helpful?

Sql How to Use Max Function in Where Clause

Source: https://www.mysqltutorial.org/mysql-max-function/