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.

Syntax

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

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:

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:

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:

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).

Why Finding the Maximum Value Across Multiple Columns is Useful?

Comparative Analysis: Comparing values across different columns within the same row is a useful operation. It helps pinpoint the maximum value, offering insights into the largest data point among the selected columns.
Conditional Queries: The maximum value plays a crucial role in constructing conditional queries. For example, you may want to retrieve records where a specific column has the highest value among multiple criteria.
Data Integrity Checks: Ensuring data integrity involves verifying the maximum value. This step confirms that the data in the specified columns aligns with the expected range or criteria.
Aggregate Reporting: In the context of generating reports or summaries, identifying the maximum value is instrumental in presenting key statistics. This proves particularly valuable when showcasing the peak value across various dimensions.

Conclusion

MySQL GREATEST function provides a convenient and efficient way to find the maximum value among multiple columns. It can be used with numeric and non-numeric records.

GREATEST function in MySQL enhances the ability of developers and analysts to derive meaningful insights from their data. By understanding the syntax and exploring practical examples, users can utilize the full potential of MySQL to navigate the complexities of identifying the maximum across diverse datasets.

Article Tags :

Databases

Geeks Premier League

MySQL

Geeks Premier League 2023