Jun 30, 2021 by Robert Gravelle
The MAX() function is often used to return the largest value of a given column. It's not picky about types, so the column may contain salaries, best before dates, or last names. The question is, can the MAX() function also find the highest value across multiple columns? The short answer is Yes. The longer explanation is that it depends on the database you're using. In today's blog, we'll explore a few ways to obtain the maximum value among two or more columns, either using the MAX() function, or an even better alternative.
If you're working with MySQL, you can combine MAX() with the GREATEST() function to get the biggest value from two or more fields. Here's the syntax for GREATEST:
GREATEST(value1,value2,...)
Given two or more arguments, it returns the largest (maximum-valued) argument. If any argument is NULL, GREATEST returns NULL.
An Example
If you're going to look for the maximum value across fields, it helps to compare columns that contain similar data - apples against apples, so to speak. The classicmodels database's products table contains two similar columns: "buyPrice" and "MSRP". Both store dollar figures as decimal data:
Ideally, the GREATEST() input parameters should be scalar values. As it happens, the MAX() function returns the largest value in a column! Here's the Query and result in Navicat for SQL Server:
Not surprisingly, the MSRP contained the highest value. Otherwise, the company might want to consider a different vendor.
For other database that don't support the GREATEST() function, there are ways to compare multiple columns using MAX(). It just takes a bit of creativity! Here are a few solutions, using SQL Server:
UNION ALL
The UNION ALL command combines the result set of two or more SELECT statements. Unlike the UNION command, UNION ALL includes duplicates. In any event, either command may be utilized to combine different columns into one long result set. Its results may then be treated as a subquery from which the maximum value is derived:
SELECT MAX(T.field) AS MaxOfColumnsFROM ( SELECT column1 AS field FROM YourTable UNION ALL SELECT column2 AS field FROM YourTable UNION ALL SELECT column3 As field FROM YourTable) AS T
Here's an example query against the Sakila Sample Database in Navicat for SQL Server that includes both the rental and return date from the rentals table:
Select MAX from VALUES
The SQL VALUES keyword is not just for INSERTs. You can also SELECT from a list of values using the following syntax:
select (values (1), (2), (3)) as temp(c)
This statement can be expanded to serve our purpose as follows:
SELECT ( SELECT MAX(myval) FROM (VALUES (column1),(column2),(column3)) AS temp(myval)) AS MaxOfColumnsFROMYourTable
We can use this template to serve as a basis for our query against the rentals table:
AS we saw here today, there are several ways to obtain the maximum value across multiple columns. These include using the GREATEST() function, or by getting a bit creative with the MAX() function.
Interested in Navicat for SQL Server? You can try it for 14 days completely free of charge for evaluation purposes.
Rob Gravelle resides in Ottawa, Canada, and has been an IT Guru for over 20 years. In that time, Rob has built systems for intelligence-related organizations such as Canada Border Services and various commercial businesses. In his spare time, Rob has become an accomplished music artist with several CDs and digital releases to his credit.
