Which SQL Keyword is used to Retrieve a Maximum Value?

What is MAX() Keyword in SQL?

As we discussed earlier, the MAX() keyword returns the maximum value. The MAX() function is an aggregate function in SQL.

An aggregate function in SQL returns a result having a single value after performing calculations on a group of values.

Note: The aggregate functions ignore the NULL values in a table’s column.

You have seen the syntax of the MAX() keyword for a single column mentioned under the topic: which SQL keyword is used to retrieve a maximum value. What if you want to group the results of one or more columns?

Syntax of MAX() for Multiple Columns

Here is the MAX() Keyword’s syntax in case of grouping the results of one or more columns.

SELECT column1, column2, ... column_n,
       MAX(aggregate_column)
FROM tables
[WHERE conditions]
GROUP BY column1, column2, ... column_n;

Let’s understand the MAX() function with the help of examples.

Example with Single Expression

The SQL MAX function can be used in the simplest way possible by returning a single item that contains the MAX value.

Consider the example of the CodingNinjas_Articles table having Article_ID, Article_Name, Subject, and Views as the fields; some data has been stored in the fields.

The following is the code for creating our table.

Create Table

CREATE TABLE CodingNinjas_Articles(
Article_ID int,
Article_name varchar(200),
Subject varchar(100),
views int
  );

Insert Data  

INSERT INTO CodingNinjas_Articles VALUES (1, 'SQL Keywords', 'DBMS', 1500);
INSERT INTO CodingNinjas_Articles VALUES (2, 'OSI Model', 'CN', 1300);
INSERT INTO CodingNinjas_Articles VALUES (3, 'Arrays', 'DSA', 1400);
INSERT INTO CodingNinjas_Articles VALUES (4, 'OOPs', 'JAVA', 1300);
INSERT INTO CodingNinjas_Articles VALUES (5, 'MAX() Keyword', 'SQL', 1600);

Using the SELECT statement, let's check the data in our table to see how it looks.

SELECT * FROM CodingNinjas_Articles

Output:

Query

Now, we want to find the value of the maximum views. So, we will use the MAX() function.

SELECT MAX(views) AS "Maximum view"
FROM CodingNinjas_Articles;

Output:

The MAX(views) field has been aliased as "Maximum view" in this SQL MAX function example. When the result set is returned, the field name will appear as "Maximum View."

Aliases are often used to improve the readability of column names. A table or a column within a table can have a temporary name using SQL aliases. By using the AS keyword, an alias is created. An alias only exists while that query is executing.

Table having NULL Value

Consider an example when our table has NULL values. The MAX() function will ignore the NULL Values.
You can update the table using the UPDATE Keyword. Here is an example:

Update Query

UPDATE CodingNinjas_articles
SET views = NULL
WHERE Article_ID = 5;

Output:

The table will appear as follows:

MAX() Query

When we execute the MAX function, it will ignore the NULL Value. Here is an example:

SELECT MAX(views) AS "Maximum view"
FROM CodingNinjas_articles;

Output:

Example with GROUP BY Clause

You can also use the MAX() function with the GROUP BY Clause. Here is the syntax.

SELECT MAX(column_name)
FROM table
GROUP BY column1, column 2….. column_n;

For example, we are using the SQL MAX function to return each subject's name and the subject's maximum views.

MAX() Query

SELECT Subject, MAX(views) AS "Maximum views"
FROM CodingNinjas_articles
GROUP BY Subject;

Output:

You can use the HAVING clause to define the condition that needs to be met to filter out specific data from a given group according to that condition.

MAX() Query with HAVING Clause

The syntax of using the HAVING keyword along with GROUP BY and MAX() keywords are as follows.

SELECT MAX(column_name)
FROM table
GROUP BY column1, column 2….. column_n
HAVING Conditions;

Here is an example of the maximum view of an article which belongs to SQL Subject. 

SELECT MAX(views) AS "Maximum views" 
FROM CodingNinjas_articles
GROUP BY Subject
HAVING Subject = 'SQL';

Output:

Example with WHERE Clause

You can also use the MAX() function with the WHERE Clause. Here is the syntax.

SELECT MAX(column_name)
FROM table
WHERE conditions;

MAX() Query with Where Clause

Let's assume you want to list the article’s and subject’s names along with the highest views they have received.

Here is the code:

SELECT Article_name, Subject, MAX(Views) 
FROM CodingNinjas_articles
WHERE views = 
(
    SELECT MAX(views)
    FROM CodingNinjas_articles
);

Output:

Frequently Asked Questions

Can I use SQL MAX Function for Non-numeric Columns?

Yes, you can use the SQL MAX function for non-numeric columns. When you use the MAX function, the lexicographic order is considered to generate the output. It compares similar characters in the two strings from left to right to identify which string comes first. For example, ‘a’ will have high priority than ‘b’. 

What are NULL values in a Table?

According to SQL, any datatype may have a NULL value. A particular label called a NULL value is used in SQL to denote a data value that doesn't exist in the database. A field with spaces or a zero value differs from one with a NULL value.

What do you mean by the Primary Key of a table?

You can uniquely identify each record in a table using the Primary Key. A Primary Key should be UNIQUE and not NULL. For example, in a student_details table, the registration number can be considered a Primary Key. There can only be one Primary Key in a table.

Conclusion

We have discussed the MAX keyword in detail. We started by asking which SQL keyword is used to retrieve a maximum value and got the answer MAX(). You can use the MAX() keyword with GROUP BY and WHERE Clauses.

We hope this blog has helped you. We recommend you visit our articles on different topics of DBMS, such as

• DBMS Architecture.
   
• DBMS Storage System.
   
• SQL vs MYSQL.
   
• TCL Commands in SQL
   

If you liked our article, do upvote our article and help other ninjas grow.  You can refer to our Guided Path on Coding Ninjas Studio to upskill yourself in Data Structures and Algorithms, Competitive Programming, System Design, and many more!

Head over to our practice platform Coding Ninjas Studio to practise top problems, attempt mock tests, read interview experiences and interview bundles, follow guided paths for placement preparations, and much more!!

Don't stop here, Ninja; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.

Happy Reading!!