MIN and MAX Aggregate Functions in SQL Server

SQL Server Functions

In database systems in general and in SQL Server in particular, functions are pieces of code that take zero or one input and return a single output or an array.

Table-Valued Functions which are typically user-defined can return an array, but in-built SQL Server functions are typically Scalar-Valued Functions. The third class of functions in SQL Server are Aggregate-Valued Functions. The MIN and MAX SQL Server functions are Aggregate-Valued Functions.

Window Functions are a relatively new class. They perform calculations like aggregate functions but do it over a set of rows related to the current row. While an aggregate function is likely to yield a single result by working on a column, a Window Function is more likely to yield a result for each row.

The SQL Server Functions classification can also ground on data type – String Functions, Numeric Functions, and Date Functions. We can deduce that String Functions operate on string values, e.g., LENGTH().

MIN & MAX

The MIN and MAX functions are very simple aggregate functions. Still, they can answer a wide variety of questions depending on the data set we are dealing with.

For instance, when we issue the SELECT statement, we are asking SQL Server a question. Hence, we say we are executing a query. To ask SQL Server about the minimum and maximum values in a column, we use the following syntax:

SELECT MIN(column_name) FROM table_name;
SELECT MAX(column_name) FROM table_name;

When we use this syntax, SQL Server returns a single value. Thus, we can consider the MIN() and MAX() functions Scalar-Valued Functions.

Listing 1 displays the simple statement to derive the minimum and maximum values on the freight weight from the Sales.Orders table of the Itzik Bengan’s TSQLV4 database:

-- Listing 1: Basic MIN() and MAX() Function Queries
USE TSQLV4
GO
SELECT * FROM [Sales].[Orders];

-- Without Column Aliases
SELECT MIN(freight) FROM [Sales].[Orders];
SELECT MAX(freight) FROM [Sales].[Orders];
-- Without Column Aliases
SELECT MIN(freight) min_freight FROM [Sales].[Orders];
SELECT MAX(freight) max_freight FROM [Sales].[Orders];

GROUP BY

“What are the minimum and maximum freight weights per country?” To answer this question, we need the GROUP BY clause. The example is in the Listing 2 below.

The query first groups the data by shipcountry, and then it returns the minimum and maximum freight weights respectively per country. We can confirm this by issuing the last query in the listing (validation).

-- Listing 2: MIN and MAX Freight By Country
-- Minimum by Country
SELECT shipcountry, MIN(freight) min_freight FROM [Sales].[Orders]
GROUP BY shipcountry;

-- Maximum by Country
SELECT shipcountry, MAX(freight) max_freight FROM [Sales].[Orders]
GROUP BY shipcountry;

-- Validation
SELECT * FROM [Sales].[Orders] WHERE shipcountry='Finland'
ORDER BY freight;

-- Listing 2: MIN and MAX Freight by Country
-- Minimum by Customer
SELECT custid, MIN(freight) min_freight FROM [Sales].[Orders]
GROUP BY custid;

-- Maximum by Customer
SELECT custid, MAX(freight) max_freight FROM [Sales].[Orders]
GROUP BY custid;


-- Validation
SELECT * FROM [Sales].[Orders] WHERE custid='23'
ORDER BY freight;

Figure 3 shows the results. We can do something similar with another column – the custid column. In this case, we are answering the question, “What is the minimum and maximum fright for each customer?”

More Questions

We can probe the data further to find out which countries have freight weights less than 1 or more than 800. We do this using the HAVING clause that filters resultsets segmented into groups by the GROUP BY clause.

-- Listing 3: Introducing the HAVING Clause
SELECT shipcountry, MIN(freight) min_freight FROM [Sales].[Orders]
GROUP BY shipcountry
HAVING MIN(freight)<1;

SELECT shipcountry, MAX(freight) max_freight FROM [Sales].[Orders]
GROUP BY shipcountry
HAVING MAX(freight)>800;

Working with Dates

When applying the MIN() and MAX() functions to dates, we expect to see the earliest and latest dates respectively. Have a look at the examples – the dates are stored as integers internally in SQL Server.

-- Listing 4: Working with Dates
SELECT MIN(orderdate) earliest_date FROM [Sales].[Orders];
SELECT MAX(orderdate) latest_date FROM [Sales].[Orders];

We can dig deeper by asking which customer made the first order and which customer made the most recent order. For that, we are using the code in Listing 5. Figure 6 shows that the custid 85 customer performed the earliest order, while the custid 9 customer placed the latest order.

-- Listing 5: Customer Order Dates
SELECT custid, MIN(orderdate) earliest_date FROM [Sales].[Orders]
GROUP BY custid
ORDER BY earliest_date;

SELECT custid, MAX(orderdate) latest_date FROM [Sales].[Orders]
GROUP BY custid
ORDER BY latest_date desc;

Conclusion

In this article, we briefly demonstrated the use of the MIN() and MAX() aggregate functions in SQL Server. The role of these functions is returning the minimum and maximum values for a given range stored in a table column.

We can answer more questions with these functions by combining them with other T-SQL commands such as the GROUP BY, ORDER BY, and HAVING clauses.

Lots of tools simplify and accelerate such task performance, and you can choose any solution that meets your requirements. Among them, SQL Complete from Devart does all the necessary manipulations and provides an in-place SSMS Grid Aggregate feature that calculates MIN, MAX, and AVG of the selected values in the grid.