How to use CASE Statement In SQL
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 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.
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, ranging from financial analysis to data reporting. In this article, we will learn about How to find the maximum of multiple columns in PL/SQL with the help of two different methods to resolve problems by various examples and so on.
Contact Us