How to Find the Maximum of Multiple Columns in SQL Server?

When working with SQL Server databases, there are times when we need to find the maximum value among multiple columns. This task can be accomplished using various techniques within SQL queries. By using functions like CASE and GREATEST, SQL Server provides efficient ways to determine the maximum value among several columns in a table. In this article, we will explore these methods in detail, offering insights into how to effectively find the maximum value across multiple columns in SQL Server.

The maximum value from the multiple columns in SQL Server is achieved through the most widely used methods which are given below:

1. Using CASE expression
2. Using GREATEST() Function

Let’s set up an environment

For better understanding, we need a table on which we will perform various operations and so on. Let’s create the table and insert some records in the table. The following code creates the SampleTable and inserts some records in the table.

Query:

CREATE TABLE SampleTable (
    id INT PRIMARY KEY,
    column1 INT,
    column2 INT
);

INSERT INTO SampleTable (id, column1, column2)
VALUES
    (1, 10, 20),
    (2, 25, 15),
    (3, 5, 40),
    (4, 30, 10);

Output:

The CASE expression allows the user to write conditions much like if-else or switch statements in SQL Server. We can use this to create condition when one column is greater than every other.

The following query does the trick to find the maximum of two columns:

Query:

SELECT
    id,
    column1,
    column2,
    CASE
      WHEN column1 > column2 THEN column1
      ELSE column2
    END AS max_value
FROM
    SampleTable;

Output:

Explanation: As we can already see if the number of columns from which we have to compare increases, the code becomes very complicated and cumbersome. The next method solves this issue.

The GREATEST() function returns the maximum value of all the arguments with the number of arguments can be anything. So using the GREATEST() function we can compare literal values as well as columns.

The following query compares the two columns that we have and returns the result as before.

Query:

SELECT
    id,
    column1,
    column2,
    GREATEST(column1, column2) AS max_value
FROM
    SampleTable;

Output:

Explanation: This above query selects the id, column1, and column2 columns from SampleTable and calculates the maximum value between column1 and column2 for each row, labeling it as max_value. This allows you to view the original values alongside their maximum in the result set.

Let’s create the table and insert some data inside it. The following query creates a SALES table and inserts three records in it.

Query:

-- create
CREATE TABLE SALES (
  product_name VARCHAR(20),
  jan INT,
  feb INT,
  mar INT
);

-- insert
INSERT INTO SALES VALUES ('Book', 123, 89, 22);
INSERT INTO SALES VALUES ('Pen', 99, 12, 51);
INSERT INTO SALES VALUES ('Sharpner', 82, 47, 90);

--to show the table
SELECT * FROM SALES;

Output:

Explanation: The above table contains the information about different products and the number of units sold in January, February, and March. So, the record (‘Book’, 123, 89, 22) states that 123 units, 89 units, and 22 units of the book was sold in January, February, and March respectively.

Now we will find out what the maximum unit of a product sold in any of the three months for all the products in the table. For this we are going to make use of the GREATEST() function we understood in method 2. As already mentioned, we can use GREATEST() function to find maximum value from more than 2 values. The following query makes use of GREATEST() function to find the maximum units sold for each product.

Query:

SELECT
    product_name,
    GREATEST(jan, feb, mar) AS max_sales
FROM
    SALES;

Output:

Explanation: As we can see that books were sold maximum number of 123 units, pens were sold maximum number of 99 units, and sharpeners were sold maximum number of 90 units.

In this article, we covered how we can find the maximum of multiple columns in SQL Server. We had a chance to look at two different methods to go about doing this, first using CASE statement. We understood the pitfalls of the CASE statement and how quickly it can get very complicated. We later looked at the GREATEST() function and understood the ease it provides. We also how we can use the concepts we learned in this article to a real-life situation through the technical example.