Finding the Maximum of Multiple Columns in MySQL Example
Let’s look at some examples of finding the maximum of multiple columns in the table.
Example 1: Maximum of Numeric Columns
So, In this Example we have created the Database as ‘db_info’ and we have a ‘sales‘ table with ‘quarter1_sales,’ ‘quarter2_sales,’ and ‘quarter3_sales.’ The goal is to find the maximum sales across these quarters.
CREATE DATABASE db_info; USE db_info; -- Create a sample 'sales' table CREATE TABLE sales ( id INT PRIMARY KEY, quarter1_sales INT, quarter2_sales INT, quarter3_sales INT ); -- Insert sample data INSERT INTO sales VALUES (1, 50000, 60000, 75000), (2, 55000, 62000, 80000), (3, 60000, 65000, 78000); -- Query to find the maximum sales across quarters SELECT GREATEST(quarter1_sales, quarter2_sales, quarter3_sales) AS max_sales FROM sales;
Output:
Explanation:
In this example, we have a ‘sales’ table with three columns representing sales figures for different quarters: ‘quarter1_sales,’ ‘quarter2_sales,’ and ‘quarter3_sales.’
The query calculates the maximum sales across these quarters using the GREATEST function. Here’s the breakdown:
- For the first row, the maximum is 75000 (quarter3_sales).
- For the second row, the maximum is 80000 (quarter3_sales).
- For the third row, the maximum is 78000 (quarter3_sales).
Example 2: Maximum of Non-Numeric Columns
Now, let’s consider a ‘products‘ table with ‘price,’ ‘discount,’ and ‘promo_price.’ We want to determine the maximum price, considering both the original price and any discounts or promotional prices.
-- SQL Code CREATE DATABASE db_info; USE db_info; -- Create a sample 'products' table CREATE TABLE products ( id INT PRIMARY KEY, price DECIMAL(8,2), discount DECIMAL(5,2), promo_price DECIMAL(8,2) ); -- Insert sample data INSERT INTO products VALUES (1, 120.00, 10.00, 0), (2, 89.99, 5.00, 0), (3, 150.00, 20.00, 120.00); -- Query to find the maximum price considering discounts and promo prices SELECT GREATEST(price, price - discount, promo_price) AS max_price FROM products;
Output:
Explanation:
The query calculates the maximum price, considering the original price, any discounts, and promotional prices. Here’s the breakdown:
- For the first row, the maximum is 120.00 (original price).
- For the second row, the maximum is 89.99 (price – discount).
- For the third row, the maximum is 150.00 (original price).
Example 3: Maximum of Different Data Types
Suppose we have a ‘products_info‘ table with information about products, including their names, release dates, and ratings. here we want to find the product with the highest rating, considering both the professional critic rating and user ratings.
-- SQL Code CREATE DATABASE db_info; USE db_info; -- Create a sample 'products_info' table CREATE TABLE products_info ( product_id INT PRIMARY KEY, product_name VARCHAR(100), critic_rating DECIMAL(3, 1), user_rating DECIMAL(3, 1) ); -- Insert sample data INSERT INTO products_info VALUES (1, 'Smartphone A', 4.5, 4.8), (2, 'Laptop B', 4.2, 4.5), (3, 'Camera C', 4.8, 4.3); -- Query to find the maximum rating across critic and user ratings SELECT product_name, GREATEST(critic_rating, user_rating) AS max_rating FROM products_info;
Output:
Explanation:
In this example, the ‘products_info‘ table contains three columns: product_id, product_name, critic_rating, and user_rating. The GREATEST function is used to find the maximum rating for each product, considering both the critic rating and user rating.
- For ‘Smartphone A’, the maximum rating is 4.8 (user rating).
- For ‘Laptop B’, the maximum rating is 4.5 (user rating).
- For ‘Camera C’, the maximum rating is 4.8 (critic rating).
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 several columns in MySQL, we use the GREATEST() function. The GREATEST() function takes multiple columns as arguments and returns the greatest (maximum) value of the respective columns.
We’ll explore the syntax and techniques for finding the maximum value among multiple columns.