SQL MAX + CASE WHEN Explained (2025 Guide)

The max case when SQL technique is a powerful pattern in data analysis that helps extract the maximum value across conditional groupings. When combined, the MAX() function and CASE WHEN clause allows analysts to find the maximum value that meets certain criteria within each group. For instance, you can identify the latest login time for active users, the highest revenue only from a specific product line, or the peak temperature on weekends.

This guide will walk you through how the SQL max function behaves on its own and how it evolves when used with conditional logic via CASE WHEN. You’ll learn how to use SQLmax case when to solve conditional pivots, flagged max value extraction, or time-based filtering by using clean, readable SQL queries. If you’ve ever needed to pull “the most recent non-null response per customer” or “the highest-rated product among those with over 100 reviews,” this method is your go-to tool.

SQL max function is a standard aggregate function used to retrieve the largest value from a column. It works with numeric, date/time, and even string values (depending on collation rules).

Basic Syntax:

SELECT MAX(sales) FROM orders;

In the example above, SQL returns the highest sales value found in the orders table

max in SQL function adapts across data types:

• Numeric columns return the largest number.
• Date/time columns return the most recent (latest) date.
• String columns (depending on collation) return the lexicographically largest value.

This function is widely supported across major database systems, including MySQL, PostgreSQL, SQL Server, Google BigQuery, and Presto. Syntax stays consistent across these platforms, though some engines may offer extensions or slightly different performance optimizations.

Think of it as SQL’s way of adding if-else logic right into your queries. It’s essentially allowing you to return different values based on conditions within a row. CASE WHEN helps adding if-else style logic into your SQL queries.

Let’s see an example:

Input Table: orders

order_id	status
101	shipped
102	pending
103	canceled
104	shipped

Here’s a basic example of case when sql:

SELECT
  order_id,
  CASE
    WHEN status = 'shipped' THEN 'Complete'
    WHEN status = 'pending' THEN 'In Progress'
    ELSE 'Unknown'
  END AS order_stage
FROM orders;

In this query, each row is checked for its status, and the result is mapped to a human-readable label.

Output:

order_id	order_stage
101	Complete
102	In Progress
103	Unknown
104	Complete

The case when SQL logic is especially useful in:

• Categorizing data into custom labels (e.g.CASE WHEN amount > 1000 THEN 1 ELSE 0)
• Driving conditional aggregations (which we’ll explore in the next section)

Whether you’re transforming values for reporting or filtering aggregations in a GROUP BY, understanding what is case in SQL lays the foundation for more advanced query logic like MAX(CASE WHEN ...).

What’s the Difference Between CASE WHEN, WHERE, and HAVING in SQL?

When writing SQL queries, it’s easy to get confused between CASE WHEN, WHERE, and HAVING. They each serve different purposes at different stages of the SQL query lifecycle. Let’s explore how they differ, how they work together, and what each one actually does under the hood.

WHERE: Filters Rows Before Any Aggregation

The WHERE clause is used to exclude rows from the dataset before any grouping or aggregation occurs. This makes it perfect for narrowing down the dataset early on. You can’t use aggregate functions here, and any logic must apply to individual rows.

Example Query:

SELECT *
FROM orders
WHERE status = 'shipped';

Input Table: orders

order_id	customer_id	amount	status
1	A1	100	shipped
2	A2	200	pending
3	A3	150	shipped
4	A1	120	canceled

Output:

order_id	customer_id	amount	status
1	A1	100	shipped
3	A3	150	shipped

This shows how WHERE filters out rows before any aggregation, leaving only those that match the condition.

---

CASE WHEN: Evaluates Conditions, Doesn’t Filter Rows

The CASE WHEN expression doesn’t remove rows—it’s used to transform values conditionally, like a column-level if-else. It can be embedded inside the SELECT, ORDER BY, or GROUP BY clauses.

Example Query:

SELECT
  order_id,
  CASE
    WHEN amount >= 150 THEN 'High'
    ELSE 'Standard'
  END AS order_tier
FROM orders;

Output:

order_id	order_tier
1	Standard
2	High
3	High
4	Standard

No rows were removed. Each row was evaluated, and the CASE logic returned a new label.

---

HAVING: Filters Groups After Aggregation

HAVING works after aggregation and is often used in combination with GROUP BY. Unlike WHERE, you can use aggregate functions like SUM(), COUNT(), and even MAX(CASE WHEN ...) inside it.

Example Query:

SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 200;

Grouped Table:

customer_id	total_spent
A1	220
A2	200
A3	150

Output:

customer_id	total_spent
A1	220

So while WHERE filters individual rows before grouping, HAVING filters entire groups after they’ve been aggregated.

Mastering the timing and behavior of each clause gives you precise control over your data—and sets the stage for advanced patterns like MAX(CASE WHEN ...), which combines aggregation and conditional logic in one powerful line.

In SQL, combining the MAX() function with a CASE WHEN expression allows for conditional aggregation. That is, finding the maximum value from a subset of rows that meet a specific condition. This technique is widely used when you want to pivot data or extract max values across specific groups or categories. For example, you might want to find the highest purchase amount per customer only for a certain product category. Instead of filtering the entire dataset, you apply logic inline to selectively evaluate only matching rows.

Syntax

MAX(CASE WHEN condition THEN value END) AS alias

Here’s the basic structure for conditional aggregation using MAX(CASE WHEN...):

SELECT
  group_column,
  MAX(CASE WHEN condition THEN value END) AS alias
FROM table
GROUP BY group_column;

This pattern works by evaluating the CASE WHEN logic inside the MAX() function. Only the rows that meet the condition return a value, others yield NULL, which MAX() ignores.

Step-by-Step Walk-Through

Let’s look at an example:

Input Table: sales_data

customer_id	product_type	amount
1	electronics	200
1	clothing	150
2	electronics	300
2	clothing	100

Goal:

Find the max electronics and max clothing purchase per customer.

SQL Query Using MAX(CASE WHEN)

SELECT
  customer_id,
  MAX(CASE WHEN product_type = 'electronics' THEN amount END) AS max_electronics,
  MAX(CASE WHEN product_type = 'clothing' THEN amount END) AS max_clothing
FROM sales_data
GROUP BY customer_id;

Output Table

customer_id	max_electronics	max_clothing
1	200	150
2	300	100

The CASE statement filters data by category, and the MAX() function finds the highest value per condition.

Common Mistakes & Quick Fixes

Here are some common mistakes in SQL max function with case statement and how to avoid them:

1. NULL Handling

CASE WHEN returns NULL if no condition is met. That’s fine, cause MAX() skips NULLs. But if your column includes only NULLs for some groups, you’ll get NULL as the result. You can wrap it with COALESCE() for default values if needed.

COALESCE(MAX(CASE WHEN ... THEN ... END), 0)

2. Data-Type Mismatches

Make sure the THEN clause returns the same data type for all branches. Mixing INT and VARCHAR, for example, can lead to casting issues or unexpected results.

3. Performance Concerns

Each MAX(CASE WHEN...) clause is evaluated per row. If you’re using many of these in a query with millions of rows, performance may drop. Consider indexing or restructuring logic using CTEs or pre-aggregated views.

Top-Seller by Month

Let’s say you manage a retail business and want to see the top-selling product per month based on units sold.

Input Table: monthly_sales

month	product	units_sold
January	Shoes	500
January	Jackets	300
February	Shoes	600
February	Jackets	700

Goal: Return product(s) with the maximum sales per month.

SELECT
  month,
  MAX(CASE WHEN units_sold = max_units THEN product END) AS top_product,
  MAX(units_sold) AS max_units
FROM (
  SELECT
    month,
    product,
    units_sold,
    MAX(units_sold) OVER (PARTITION BY month) AS max_units
  FROM monthly_sales
) sub
GROUP BY month;

Output

month	top_product	max_units
January	Shoes	500
February	Jackets	700

This approach highlights the use of case when max SQL for conditional selection of values in grouped data. You’ll often see this logic when building dashboards or top-N reports. If, instead, you wanted to filter using the WHERE clause, you’d use a subquery with SQL max value in where clause.

Latest Login per User

In user analytics, it’s common to track the last login date of each user, but sometimes only under specific login types (e.g., “mobile” or “web”).

Input Table: user_logins

user_id	login_type	login_time
1	web	2024-06-01 09:00:00
1	mobile	2024-06-02 15:00:00
2	web	2024-06-03 08:30:00
2	mobile	2024-06-01 20:00:00

Goal: Find the most recent mobile login per user.

SELECT
  user_id,
  MAX(CASE WHEN login_type = 'mobile' THEN login_time END) AS latest_mobile_login
FROM user_logins
GROUP BY user_id;

Output

user_id	latest_mobile_login
1	2024-06-02 15:00:00
2	2024-06-01 20:00:00

This is a classic use of SQL max with condition, filtering the aggregation only to rows matching a specific login_type. If you were filtering for rows in the query with MAX(login_time) only, you’d use a correlated subquery or a join with a derived table using SQL max in where clause.

Highest Score by Category

For SQL interviews, a favorite challenge involves finding the highest test score per category, along with the user who achieved it.

Input Table: test_scores

category	user_id	score
Math	1	85
Math	2	92
English	1	88
English	3	90

Goal: Return top scorer per category.

SELECT
  category,
  MAX(score) AS top_score
FROM test_scores
GROUP BY category
HAVING MAX(score) IS NOT NULL;

This uses the SQL having max pattern to filter grouped results. To also show the top user, wrap with a subquery or use a JOIN with a derived table.

With Top Scorer:

SELECT
  t.category,
  t.user_id,
  t.score
FROM test_scores t
JOIN (
  SELECT category, MAX(score) AS top_score
  FROM test_scores
  GROUP BY category
) m ON t.category = m.category AND t.score = m.top_score;

Output

category	user_id	score
Math	2	92
English	3	90

You now have a having max SQL query that selects top values with join logic—perfect for showing best performers or winners in any grouped data.

Prompt

“Top Product per Category Based on Recent Monthly Sales”

You’re given a table of sales transactions where each row represents the number of units sold for a product in a specific month. Each product belongs to a product category. Your task is to write a SQL query that returns, for each category, the product with the highest number of units sold in the most recent month available for that category.

Return the category, month, top_product, and units_sold.

Input Example

category	product_name	month	units_sold
Books	Novel A	2024-05-01	300
Books	Novel B	2024-06-01	500
Books	Novel C	2024-06-01	450
Tech	Laptop X	2024-05-01	200
Tech	Laptop Y	2024-06-01	250
Tech	Laptop Z	2024-06-01	300
Tech	Laptop X	2024-06-01	150

---

Expected Output

category	month	top_product	units_sold
Books	2024-06-01	Novel B	500
Tech	2024-06-01	Laptop Z	300

---

Step-by-Step SQL Solution

To solve this, we must:

1. Identify the most recent month per category
2. Filter data down to that month
3. Find the product(s) with the highest units_sold
4. Use MAX(CASE WHEN...) to select the matching product

This is a textbook use of max case when SQL logic to conditionally select values within grouped data.

Starter Schema

CREATE TABLE product_sales (
  category VARCHAR,
  product_name VARCHAR,
  month DATE,
  units_sold INT
);

Step 1: Find the latest month per category

SELECT
  category,
  MAX(month) AS latest_month
FROM product_sales
GROUP BY category

Step 2: Join back to the original table

WITH latest_sales AS (
  SELECT
    ps.*
  FROM product_sales ps
  JOIN (
    SELECT
      category,
      MAX(month) AS latest_month
    FROM product_sales
    GROUP BY category
  ) lm
  ON ps.category = lm.category AND ps.month = lm.latest_month
)

Step 3: Find the top product per category using SQL max case when

, ranked_sales AS (
  SELECT *,
         MAX(units_sold) OVER (PARTITION BY category) AS max_units
  FROM latest_sales
)
SELECT
  category,
  month,
  MAX(CASE WHEN units_sold = max_units THEN product_name END) AS top_product,
  MAX(units_sold) AS units_sold
FROM ranked_sales
GROUP BY category, month;

Final Output

category	month	top_product	units_sold
Books	2024-06-01	Novel B	500
Tech	2024-06-01	Laptop Z	300

Complexity Notes

Time complexity is essential, especially if you’re an ML engineer or data analyst. Check out our time complexity guide to strengthen your foundations.

In this case:

• Time Complexity:

  O(n log n) for window functions and grouping

• Space Complexity:

  O(n) for intermediate CTEs

Looking to deepen your understanding of MAX(), CASE WHEN, and their variations in SQL? Here are some targeted practice problems to stretch your skills beyond just max case when SQL scenarios. These questions touch on aggregation, partitioning, filtering, and conditional logic in real-world data queries.

Schemas we’ll use:

• orders(order_id, user_id, purchase_date, amount, category_id, product_id)
• employees(emp_id, department, salary)
• students(student_id, class, score)
• projects(project_id, emp_id, performance_score, start_date)
• logins(user_id, login_date)

1. Earliest purchase date per user, but only for orders over $100

Schema: orders

Practice goal: Use MIN() with a CASE WHEN filter

SELECT
    user_id,
    MIN(CASE WHEN amount > 100 THEN purchase_date END) AS earliest_over_100
FROM orders
GROUP BY user_id;

---

2. Departments with >10 employees AND containing the max salary

Schema: employees

Practice goal: Combine MAX() with HAVING

SELECT department
FROM employees
GROUP BY department
HAVING COUNT(*) > 10
   AND MAX(salary) = (SELECT MAX(salary) FROM employees);

---

3. Top product per category by average monthly sales

Schema: orders

Practice goal: Apply MAX() over partitions with ROW_NUMBER()

WITH monthly_sales AS (
    SELECT
        category_id,
        product_id,
        AVG(amount) AS avg_monthly_sales
    FROM orders
    GROUP BY category_id, product_id
)
SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY avg_monthly_sales DESC) AS rn
    FROM monthly_sales
) ranked
WHERE rn = 1;

---

4. Top-scoring student in each class

Schema: students

Practice goal: Compare ROW_NUMBER() vs MAX() OVER

-- Variant A: Using ROW_NUMBER
SELECT student_id, class, score
FROM (
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY class ORDER BY score DESC) AS rn
    FROM students
) ranked
WHERE rn = 1;

-- Variant B: Using MAX() OVER
SELECT student_id, class, score
FROM (
    SELECT *,
           MAX(score) OVER(PARTITION BY class) AS max_score
    FROM students
) s
WHERE score = max_score;

---

5. Minimum project score per employee for projects after 2023

Schema: projects

Practice goal: Use MIN() with conditional filtering inside aggregation

SELECT
    emp_id,
    MIN(CASE WHEN start_date >= '2023-01-01' THEN performance_score END) AS min_score_after_2023
FROM projects
GROUP BY emp_id;

---

6. Number of users on the most active login day

Schema: logins

Practice goal: Use nested aggregation with MAX(COUNT(...))

WITH daily_counts AS (
    SELECT login_date, COUNT(DISTINCT user_id) AS users_logged_in
    FROM logins
    GROUP BY login_date
)
SELECT login_date, users_logged_in
FROM daily_counts
WHERE users_logged_in = (SELECT MAX(users_logged_in) FROM daily_counts);

MAX() in the WHERE Clause

Using the SQLmax function in where clause can be tricky. Since aggregate functions like MAX() are not allowed directly in WHERE, we must use a subquery or a CTE.

Example: Find employee(s) with the highest salary

Schema:

employees(employee_id INT, department VARCHAR, salary INT)

Using a Correlated Subquery (max in where clause SQL):

SELECT *
FROM employees e
WHERE salary = (
  SELECT MAX(salary)
  FROM employees
);

Using a CTE for clarity:

WITH max_salary AS (
  SELECT MAX(salary) AS top_salary
  FROM employees
)
SELECT *
FROM employees
JOIN max_salary
ON employees.salary = max_salary.top_salary;

Both queries return employees tied for the top salary. Using a CTE makes the logic reusable and often easier to debug.

MAX() with GROUP BY + HAVING

This is useful when you want to filter grouped results based on the maximum values—commonly seen in sales, revenue, or activity metrics. If you haven’t learned GROUP BY yet, check out our

Complete Guide to GROUP BY to master grouping and summarizing data in SQL then come back to this excercise!

Example: Return departments whose max salary exceeds 100K

SELECT department, MAX(salary) AS max_salary
FROM employees
GROUP BY department
HAVING MAX(salary) > 100000;

This demonstrates the SQL having max value in group pattern.

Common Pitfalls:

• Using HAVING MAX(...) without a corresponding GROUP BY.
• Forgetting to include aggregates only, not raw columns, in HAVING.

You’ll use SQL max with group by regularly when filtering summary tables.

We also cover the most common SQL mistakes and show you exactly how to avoid them in your queries.

MAX() Across Multiple Columns

SQL’s MAX() only works vertically (across rows), not horizontally (across columns). To get the max of two or more columns in the same row, use functions like GREATEST() (PostgreSQL, BigQuery) or CASE WHEN logic for systems that don’t support it.

Example: Get the max score out of math and science columns

Schema:

student_scores(student_id INT, math_score INT, science_score INT)

PostgreSQL / BigQuery:

SELECT
  student_id,
  GREATEST(math_score, science_score) AS highest_score
FROM student_scores;

Standard SQL fallback:

SELECT
  student_id,
  CASE
    WHEN math_score >= science_score THEN math_score
    ELSE science_score
  END AS highest_score
FROM student_scores;

This solves for SQL max of two values, which can be extended to 3+ columns using nested CASE or chained GREATEST().

Q: What is the difference between MAX() and COUNT()?

MAX() returns the highest value in a column (like max salary), while COUNT() tallies how many rows match a condition. For example, SQL max count might mean: “What’s the maximum number of orders a user has made?” That’s different from count max SQL, which could ask, “How many users had the maximum score?”

Q: Can I use MAX() in Access?

Yes! In Microsoft Access, the MAX() function works inside aggregate queries. For example:

SELECT Category, MAX(Price) FROM Products GROUP BY Category;

This is the standard way for how to use max function ms access to get the highest value per group.

Q: How do I find the max date per ID?

Use GROUP BY with MAX() or a window function. For instance:

SELECT user_id, MAX(login_date) FROM logins GROUP BY user_id;

This is the classic SQL select row where max value pattern.

If comparing two dates in a row, use GREATEST(date1, date2) for the SQL max between two values case.

Q: How do I debug a NULL result in a MAX(CASE WHEN...) query?

Most likely, the condition inside the CASE is never met. Use a fallback like ELSE 0 or COALESCE() to handle nulls:

MAX(CASE WHEN condition THEN value ELSE 0 END)

This helps when values get swallowed inside case SQL blocks due to missing matches.

• Use plain MAX() when you need the highest value across a column such as the latest date, the highest salary, or the max score in a dataset.
• Use MAX() + CASE WHEN when you need to compute the max conditionally. For example, the max value only when a type is ‘A’, or to pivot top values across multiple categories. This is the core pattern behind many max case when SQL and SQL max case when use cases.
• When comparing multiple columns within the same row, use GREATEST() (or a CASE fallback) for horizontal comparison.
• Combine MAX() with GROUP BY and HAVING to filter groups based on their highest values, especially useful in top-performer or threshold-type queries.
• Use a correlated subquery or CTE when you need MAX() in the WHERE clause. Direct use isn’t valid due to SQL’s aggregation rules.

Now you know how to use CASE WHEN in your queries! If you run into difficulties, the SQL official documentation is always a solid reference to help you organize your syntax.

Ready to practice more with CASE WHEN? Try combining it with other functions for advanced analytics:

• SQL CASE WHEN with SUM – Calculate conditional totals directly in your queries.
• SQL CASE WHEN with COUNT – Learn how to count rows only when certain criteria are met.

Next, practice more SQL interview questions on Interview Query to strengthen both your practical and theoretical skills. Explore Interview Query’s SQL Interview Learning Path to cover all the essential topics and master SQL from basics to advanced interview prep.

Curious about what SQL questions tech companies actually ask? Check out the guide on common SQL interview questions for business analysts to cover the essentials. And when you’re ready, try our AI interviewer to simulate live coding sessions and get instant feedback.