How to Find the Maximum of Multiple Columns in SQL Server?
Last Updated :
16 Feb, 2024
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.
How to Find the Maximum of Multiple Columns?
The maximum value from the multiple columns in SQL Server is achieved through the most widely used methods which are given below:
- Using CASE expression
- 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:
Table Created1. Using CASE expression
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:
OutputExplanation: 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.
2. Using GREATEST() Function
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:
OutputExplanation: 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.
More Technical Example
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:
OutputExplanation: 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:
OutputExplanation: 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.
Conclusion
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.
Similar Reads
How to Find the Maximum of Multiple Columns in SQL
Finding the maximum value of multiple columns is one of the most common analytical tasks essential for making decisions and analyzing data. Using the MAX() function of SQL, users can find the maximum value in a single column. But to find the maximum value in multiple columns, users need to use other
2 min read
How to Find the Maximum of Multiple Columns in PostgreSQL?
PostgreSQL is one of the most advanced general-purpose object-relational database management systems and is open-source. Being an open-source software, its source code is available under PostgreSQL license, a liberal open-source license. Anyone with the right skills is free to use, modify, and distr
4 min read
How to Find the Maximum of Multiple Columns in PL/SQL?
In PL/SQL finding the maximum value of multiple columns is a common requirement for maintaining the database. This operation is important for various applications, from financial analysis to data reporting. In this article, we will learn How to find the maximum of multiple columns in PL/SQL with the
5 min read
How to Find the Maximum of Multiple Columns in SQLite?
SQLite is a serverless architecture that does not require any server to perform operations and queries. It is widely used in embedded systems, mobile applications, and small-scale web applications because of its simplicity, efficiency, and portability. SQLite supports most of the standard SQL featur
4 min read
How to Find the Maximum of Multiple Columns in MySQL?
Database management often involves the need to extract meaningful insights from multiple columns. One common challenge is identifying the maximum value across these columns. MySQL, a robust database management system, offers effective functionality for such tasks. To find the maximum value among sev
5 min read
How to SELECT DISTINCT on Multiple Columns in SQL?
In the world of databases, data duplication can lead to confusion and inefficiency. SQL provides a powerful tool, SELECT DISTINCT, to retrieve unique values from columns. However, when dealing with multiple columns, the approach becomes more detailed. In this article, we will explain how to use SELE
4 min read
How to SELECT DISTINCT on Multiple Columns in SQL Server?
When working with SQL Server, there are scenarios where we might need to retrieve unique combinations of values from multiple columns. This is where the SELECT DISTINCT statement comes in handy. It allows us to eliminate duplicate rows from the result set. However, using SELECT DISTINCT it on multip
4 min read
How to Alter Multiple Columns at Once in SQL Server?
In SQL, sometimes we need to write a single query to update the values of all columns in a table. We will use the UPDATE keyword to achieve this. For this, we use a specific kind of query shown in the below demonstration. For this article, we will be using the Microsoft SQL Server as our database an
3 min read
How to Get the Data Type of Columns in SQL Server?
SQL Server is a widely used Relational Database Management System (RDBMS) that allows users to create and manage databases effectively. SQL Server offers the SQL Server Management Studio which defines the database development and administration. In this article, we will learn how to retrieve the dat
4 min read
How to Search For Column Names in SQL?
In SQL, sometimes we need to search the column names in a table using the prefixes. For this article, we will be using the Microsoft SQL Server as our database and Select keyword. Step 1: Create a Database. For this use the below command to create a database named GeeksForGeeks. Query: CREATE DATABA
2 min read