Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql
databases
communitycreator

What is the SQL MAX() function?

NDUKWE CHIDERA K.

Grokking Modern System Design Interview for Engineers & Managers

Ace your System Design Interview and take your career to the next level. Learn to handle the design of applications like Netflix, Quora, Facebook, Uber, and many more in a 45-min interview. Learn the RESHADED framework for architecting web-scale applications by determining requirements, constraints, and assumptions before diving into a step-by-step design process.

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
INSERT INTO purchase (goodName, priceInNaira)
VALUES 
("school bag,123),
("sandal",1000),
("books",500),
("chair",300),
("pen",500),
);
insert query

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

purchase relation

id

goodName

priceInNaira

1

school bag

123

2

sandals

1000

3

books

500

4

chair

300

5

pen

500

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

SELECT MAX(priceInNaira) AS MostCost
FROM purchase
WHERE id > 0; 

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

MAX() query output

RELATED TAGS

sql
databases
communitycreator

Grokking Modern System Design Interview for Engineers & Managers

Ace your System Design Interview and take your career to the next level. Learn to handle the design of applications like Netflix, Quora, Facebook, Uber, and many more in a 45-min interview. Learn the RESHADED framework for architecting web-scale applications by determining requirements, constraints, and assumptions before diving into a step-by-step design process.

Keep Exploring