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 help of two different methods, the GREATEST function and the CASE statement to resolve problems by various examples.

Method 1: Using the GREATEST Function

To find the maximum of multiple columns in PL/SQL, the GREATEST function is utilized. This function compares multiple expressions and returns the greatest value among them. The syntax for using the GREATEST function is as follows:

Syntax

SELECT GREATEST(column1, column2, column3, ...)
AS max_value FROM table_name;

Explanation:

• GREATEST: It is a function that is used to find the greatest value among a list of expressions.
• AS: This is a keyword in SQL used to alias the result of a query. In this case, max_value is the alias given to the result of the GREATEST() function.
• max_value: This is the alias assigned to the result of the GREATEST() function. It represents the maximum value among the specified columns.
• FROM: It is a keyword that is used to specify the table from which data will retrieved.
• table_name: This is the name of the table from which data will retrieved.

Examples of Using GREATEST Function

Example 1: Let's Say We Have a Table Called "Students" with the Following Data

We can create the Students table using the following code which defines the table structure with columns such as Stu_name, hindi, English and maths as Columns.

Query:

CREATE TABLE students (
    Stu_name VARCHAR(50),
    hindi INT,
    english INT,
    maths INT
);
INSERT INTO students (Stu_name, hindi, english, maths) VALUES 
('John', 85, 90, 95),
('Alice', 78, 92, 88),
('Bob', 92, 85, 90),
('Emma', 80, 88, 92),
('Michael', 95, 82, 89);

Output:

Suppose we have to find the maximum mark for each student. we have to find the maximum mark for each student using the following query.

Query:

SELECT Stu_name, 
       GREATEST(hindi, english, maths) AS max_mark 
FROM students;

Output:

Explanation: We can observe that the desired output is obtained. The query identifies the highest score among hindi, english, and maths for each student.

Method 2: Using the CASE Statement

An alternative approach to finding the maximum value of multiple columns in PL/SQL is by using the CASE statement. The syntax for using the CASE statement to find the maximum value is as follows.

Syntax

SELECT 
  CASE 
     WHEN column1 >= column2 AND column1 >= column3 THEN column1
     WHEN column2 >= column1 AND column2 >= column3 THEN column2
     ELSE column3
  END AS max_value
FROM table_name;

Explanation:

• SELECT: This is a keyword in SQL used to retrieve data from a database.
• CASE: The CASE statement is a conditional expression used to evaluate multiple conditions and return a value based on the first condition that is true.
• >=: Greater than or equal to comparison operator.
• WHEN: This keyword is used within the CASE statement to specify the condition that is being evaluated.
• THEN: This keyword is used in the CASE statement to specify the value that should be returned if the condition specified in the WHEN clause is true.
• ELSE: This keyword is used in the CASE statement to specify the value that should be returned if none of the conditions specified in the WHEN clauses are true.
• AS max_value: This part of the query assigns an alias "max_value" to the result of the CASE statement, providing a name for the calculated maximum value.
• FROM table_name: This specifies the table from which the data is being retrieved. "table_name" is a placeholder for the actual name of the table in the database.

Examples of Using CASE Statement

Example 1: Let's Say We Have a Table Called "sales_data" with the Following Data

We can create the sales_data table using the following code which defines the table structure with columns such as product_id, sales_jan, sales_feb and sales_mar as Columns.

Query:

CREATE TABLE sales_data (
    product_id INT,
    sales_jan INT,
    sales_feb INT,
    sales_mar INT
);
INSERT INTO sales_data (product_id, sales_jan, sales_feb, sales_mar) VALUES 
(1, 100, 120, 110),
(2, 150, 130, 140),
(3, 90, 95, 100),
(4, 80, 85, 90),
(5, 200, 180, 210);

Output:

Explanation: We have fetched the output using CASE Statement.

Now, we need to find the maximum sales for each product_id. We can achieve this using the following query.

Query:

SELECT 
    product_id,
    CASE 
        WHEN sales_jan >= sales_feb AND sales_jan >= sales_mar THEN sales_jan
        WHEN sales_feb >= sales_jan AND sales_feb >= sales_mar THEN sales_feb
        ELSE sales_mar
    END AS max_sales
FROM sales_data;

Output:

Explanation: We can observe that the desired output is obtained. The query evaluates each column and determines the maximum sales figure for each product_id.

Conclusion

Overall we can say that by understanding both the GREATEST function and the CASE statement we can effectively find the maximum value among multiple columns in PL/SQL. While GREATEST offers a simpler syntax, the CASE statement allows for more complex conditional logic, making both approaches useful depending on the specific requirements.