What is the SQL MAX() function?

Introduction

A database is a collection of tables that holds data about a large entity set. These tables hold data about a particular entity in rows and columns. A column is a piece of data common to all records in the table. A row holds information about a single item on a table.

We can write from very simple to complex data manipulation and definition operations on a database table with the structured query language.

Commands which are used for such manipulations and definitions include:

• INSERT
• SELECT
• DELETE
• UPDATE
• CREATE
• MERGE, and many more.

SQL also has some arithmetic functions for carrying arithmetic calculations and comparisons on table columns and rows.

One such arithmetic function used for comparison in SQL is the MAX() function.

What is the SQL MAX() function?

The MAX() function will fetch the largest value from the column of interest. It basically compares the values of the column in a database and returns the biggest value.

How to use the SQL MAX() function

SELECT MAX(column_name)
FROM table_name
WHERE condition; 

• First, you must indicate that you are making a select query using the SELECT keyword.

• Secondly, you call the MAX(), passing to it as arguments the table column_name that you wish to select its maximum value.

• Next, use the FROM command to indicate the table being queried.

• Finally, write down the conditions, if any, in the WHERE clause.

Now, run your query, and you should have the largest value from your table returned.

Example using SQL MAX() function

Creating a goods table

For our example in this shot, we create a table named purchase.

The SQL to create the table

CREATE TABLE purchase(
id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
goodName varchar(200),
price int(200),
);

The SQL to insert into the purchase table

With the queries that have been executed, our new table will now look like this.

Now let’s use the MAX() function to get the largest price value from this table.

The select query using the MAX() function will be as below.