How to Find the Maximum of Multiple Columns in PostgreSQL?
Last Updated :
12 Feb, 2024
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 distribute PostgreSQL in any form. As it is highly stable, very low effort is required to maintain this DBMS. In this article, we will explore how one can find the maximum of multiple columns in PostgreSQL.
Finding the Maximum Value Among Multiple Columns in PostgreSQL
Before we delve deeper into the queries, let us start by creating the table and inserting some sample values in the table. The following code creates the Sample Table and inserts four entries in the table.
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);
The following is the current data in the table:
SampleTableWe are going to have a look at two methods in this article to go about finding the maximum of multiple columns of the table.
Method 1: Using CASE expression
The CASE expression allows the user to write conditions much like if-else or switch statements in PostgreSQL. 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:
SELECT
id,
column1,
column2,
CASE
WHEN column1 > column2 THEN column1
ELSE column2
END AS max_value
FROM
SampleTable;
Output:
Using CASE expression Explanation: As you 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.
Method 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:
SELECT
id,
column1,
column2,
GREATEST(column1, column2) AS max_value
FROM
SampleTable;
Output:
Using GREATEST Function
Example of Find the Maximum of Multiple Columns
Let's now use the concepts we have learned in this article in a technical example.
First, let's create the table and insert some data inside it. The following query creates a sales table and inserts three records in it.
-- 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);
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.
The following query returns the initial data in the table:
SELECT * FROM SALES;
Output:
Initial dataNow let's try to 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:
SELECT
product_name,
GREATEST(jan, feb, mar) AS max_sales
FROM
SALES;
Output:
Query outputAs you 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 PostgreSQL. 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 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 val
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 Get Multiple Counts With Single Query in PostgreSQL?
Efficient data analysis often requires counting occurrences of different categories within a dataset. PostgreSQL, a powerful relational database management system offers a feature that allows us to achieve this efficiently. In this article, we'll explore how to Get Multiple Counts With a Single Quer
3 min read
if-else to find the maximum of two numbers.
In this article, we will discuss how to find the maximum of two numbers with its working example in the R Programming Language using R if-else conditions. Syntax:max_number <- if (condition) { # Code block executed if the condition is TRUE value_if_true } else { # Code block executed if the condi
2 min read
How to Get the Type of Columns in SQL
In SQL, the types of columns in a database table play a fundamental role in data management and query execution. Each column is designed to store specific types of data, such as numbers, text, dates, or binary data. Understanding these column types is essential for effective database design, query o
4 min read
PHP Program to Find the Maximum Element in a Matrix
Given a matrix, the task is to find the maximum element of the matrix (arrays of arrays) in PHP. Finding the maximum element in a matrix is a common task that involves iterating through all the elements of the matrix and keeping track of the largest value found. Example: Input: matrix = [ [1, 2, 3],
4 min read
How to Efficiently Convert Rows to Columns in PostgreSQL?
Converting rows to columns, often referred to as pivoting or transposing, is a crucial aspect of data transformation in SQL. This technique is useful for improving data readability, facilitating analysis, aligning data formats with the requirements of reporting tools, and optimizing queries. In Post
5 min read