SQL Min and Max Functions

November 12, 2022 | by Arround The Web | No comments

“Whether working with ANSI SQL or other SQL flavors, you are bound to come across min(), and max() functions in aggregate operations. They are some of the essential functions for simple tasks and complex aggregation operations.

This article will explore what each function does, its corresponding syntax, and practical examples of when to use these functions.

NOTE: This tutorial will illustrate how to use these functions in ANSI SQL, also known as Standard SQL. However, they should perform similar actions even in other SQL flavors.”

SQL Min() Function

Let us start with the min() function. This function allows you to fetch the smallest value in a set of given values. You can provide the input values as a numerical column or a result of an expression that returns a numerical value.

The min() function is an aggregate function and returns a single numeric value from a given input.

The function syntax can be expressed as shown:

MIN(expression)

The function accepts a single argument as an expression or a given column set.

Take the example below:

SELECT
MIN(x) AS smallest
FROM
unnest([100, 102, 2, 233, 11, 12]) AS x;

Running the code above should return the smallest value from the given array.

smallest|
--------+
2|

SQL Max() Functions

As the name suggests, the max() function allows you to get the largest number in a given set of numerical values. Similarly, you can pass the set of values as a numerical column or a result of a given expression that returns a numerical value.

The function syntax is as shown:

MAX(expression)

Consider the example below:

SELECT
MAX(x) AS largest
FROM
unnest([100, 102, 2, 233, 11, 12]) AS x;

The code above should fetch the largest value from the given array. An example output is as shown:

largest|
-------+
233|

Sample Data

Let us use accurate world data best to illustrate various uses of the min and max functions. Take the example data shown below:

CREATE DATABASE local_db;
USE local_db;
CREATE TABLE DATABASES(
id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
server_name VARCHAR(50),
server_address VARCHAR(255) NOT NULL,
compression_method VARCHAR(100) DEFAULT 'none',
size_on_disk FLOAT NOT NULL,
size_compressed FLOAT,
total_records INT NOT NULL
);
INSERT INTO DATABASES(server_name, server_address, compression_method, size_on_disk, size_compressed, total_records)
VALUES ('MySQL', 'localhost:3306', 'lz77', 90.66, 40.04, 560000),
('Redis', 'localhost:6307', 'Snappy', 3.55, 998.2, 100000),
('PostgreSQL', 'localhost:5432', 'pglz', 101.2, 98.01, 340000),
('Elasticsearch', 'localhost:9200', 'lz4', 333.2, 300.2, 1200000),
('MongoDB', 'localhost:27017', 'Snappy', 4.55, 4.10, 620000),
('Apache Cassandra', 'localhost:9042', 'zstd', 300.3, 200.12, 10000000);

SELECT * FROM DATABASES;

NOTE: The data above is purely fictional and does not represent actual database capabilities or compression ratio.

Output table:

Example 1 – Using Min and Max Functions on Single Column

We can use the min and max functions on a given table column to fetch the smallest and largest value from the column, respectively.

For example, to get the database with the smallest size on disk, we can run the query as:

SELECT
MIN(size_on_disk) AS smallest_size
FROM
DATABASES;

The query above should return the smallest size from the size_on_disk column, as shown in the output below:

smallest_size|
-------------+
3.55|

For the largest size, use the max function:

SELECT
MAX(size_on_disk) AS largest_size
FROM
DATABASES;

Result:

largest_size|
------------+
333.2|

Example 2 – Using Min and Max Functions With GROUP BY

We can also include other columns using the group by function when working with an aggregate function.

For example, to show the server_name:

SELECT
MAX(size_on_disk) AS largest_size,
server_name
FROM
DATABASES
GROUP BY server_name;

The group by clause is instrumental when you categorize data into specific partitions and find a minimum or maximum value from each division.

Example 3 – Using Min and Max in the Same Query

We can also use two aggregate functions in the same query, as shown in the output below:

SELECT
MAX(size_on_disk) AS largest_size,
MIN(size_on_disk) AS smallest_size
FROM
DATABASES;

An example output is as shown:

largest_size|smallest_size|
------------+-------------+
333.2| 3.55|

Example 4 – Combining Min and Max Functions With the Having Clause

The example below shows how to use the min and max() functions to filter results using the having clause.

SELECT server_name, MAX(total_records) AS largest
FROM DATABASES GROUP BY server_name HAVING MAX(total_records) = 100000;

Resulting output:

server_name|largest|
-----------+-------+
Redis | 100000|

Closing

In this post, we extensively explored how to use the min and max() functions in SQL. We also discussed practical examples of using these two functions in your applications.

Thanks for reading!!

Source: linuxhint.com