MySQL Select MAX()

In SQL language, the MAX() aggregation function is used to return the maximum value of a column in a recordset. The function can be applied to numeric or alphanumeric data. For example, it is possible to search for the most expensive product in a table of an online store.
 

Syntax:

The syntax of the SQL query to return the maximum value of the column “column_name” is the following:

SELECT MAX(column_name) FROM table

When this function is used in combination with the GROUP BY command, the query may look like the example below:

SELECT column1, MAX(column2)
FROM table
GROUP BY column1

 

 

Example :

Let’s imagine an e-commerce site that has a “product” table on which you can find computer and furniture products.

+------+----------+------------+-------+---------+
|  id  |   name   | category   | stock |  price  |
+------+----------+------------+-------+---------+
|  101 | Monitor  | computer   |  45   | 100     |
|  102 | CD-ROM   | computer   |  100  | 40      |
|  103 | Modem    | computer   |  12   | 20      |
|  104 | Speaker  | computer   |  11   | 3       |
|  105 | Pencil   | furniture  |  148  | 2       |
|  106 | Gum      | furniture  |  250  | 1       |
+------+----------+------------+-------+---------+

 

Find the biggest value:

To get only the biggest price in the table, you can use the following query:

SELECT MAX(price) FROM products

The result is as follows:
 
Output:

+----------------------------------------+
| Max(price)                             |
+----------------------------------------+
| 100                                    |
+----------------------------------------+

 

 

Find out which record has the biggest value

It is also possible to display the name of the most expensive product at the same time as displaying its price. To do this, simply use the MAX() aggregation function while selecting the other columns. The SQL query is then the following:

SELECT id, name, MAX(price) FROM products

The result is as follows:
 
Output:

+------+----------+---------+
|  id  |   name   |  price  |
+------+----------+---------+
|  101 | Monitor  | 100     |
+------+----------+---------+

 

MAX() function with a GROUP BY:

It is possible to know the max price for each category. This is done using the following query:

SELECT `category`, MAX(price)
FROM `products`
GROUP BY `category`

The result is as follows:
 
Output:

+-----------+------------+
| category  | MAX(price) |
+-----------+------------+
| computer  | 100        |
| furniture | 2          |
+-----------+------------+

 

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More