SQL MAX Function

The SQL MAX Function is one of the SQL Aggregate Function, which is used to find the Maximum value from the total records (or rows) selected by the SQL SELECT Statement.

TIP: MAX Function will ignore Null values.

SQL Max Function Syntax

The basic syntax of the Max in SQL Server is as shown below:

In this article we will show you, How to write Max in SQL Server 2014 with example. For this, We are going to use the below shown data

SQL MAX Example

The Max in SQL Server simply returns the Maximum value from the total records present in the specified column. For example, The following query will find the Maximum yearly income present in the [Yearly Income] column from Customers table.

T-SQL CODE

OUTPUT

SQL MAX Function with Group By Clause

In most cases we usually check maximum product price (Highest pricing product) belongs to particular category or color etc. In these situations we use SQL GROUP BY Clause to group the products by color or category and then use the Max Function to find the Highest pricing product present in each group. Let us see the Example

T-SQL CODE

Above SQL Query will find, the Customers associated with the particular Department and finds the maximum income of a customer present in each department

OUTPUT

SQL Max Function with Having Clause

When we are grouping the data, In some cases we usually check for the conditions against the aggregated data. In these situations we use SQL HAVING Clause along with Group By Statement. For example, The following query will group the Customers by their Occupation and then finds the Maximum yearly Income present in each group.

T-SQL CODE

Below lines of code will check whether the aggregated amount (Maximum Yearly income of each individual Group) is greater than 50000 or not. If this is True then corresponding records will be displayed.

OUTPUT

SQL Max Function in Sub Query

When we are retrieving the data, In some cases we usually check for the conditions against the aggregated data. In these situations we have to use Sub queries in SQL Where Clause. For example, The following query will return all the Customers present in the customers table whose [Yearly Income] is exactly equal to the Maximum yearly income.

T-SQL CODE

From the first example you can observe that, Maximum Yearly Income is 90000. So above query will display all the customers from the Customers table whose yearly income is equal to 90000

OUTPUT

Thank You for Visiting Our Blog