MAX

Hook: Interviewers love MAX because it looks tiny, but it checks whether you understand aggregation, NULL handling, and how SQL finds “the biggest” value.

Question: What does MAX do in SQL?

Answer: MAX returns the largest value in a column or expression. It is an aggregate function, which means it combines many rows into one result, and it ignores NULL values. You can use it with numbers, dates, and often text, depending on the database’s sorting rules.

Interview-Ready Answer: MAX is an aggregate function that returns the highest non-NULL value in a set of rows. I use it when I want the latest date, highest price, or top score, and if all rows are NULL it returns NULL. One detail I always remember is that it can also work per group with GROUP BY, so I can get the maximum for each department, customer, or day.

What MAX is

Detailed Explanation: Think of MAX as a “keep the best so far” function. As rows flow through the query, SQL compares each value with the current winner and keeps the larger one. That makes it an aggregate function: many input rows, one output value per group.

How it works under the hood

1. SQL reads rows from the input set, either the whole table or each GROUP BY group.
2. It starts with no winner yet. The first non-NULL value becomes the current maximum.
3. For each next value, SQL compares it with the current maximum using the type’s ordering rules.
4. If the new value is larger, it replaces the current maximum; otherwise the current maximum stays.
5. At the end of the group, SQL returns the final winner.

Important: NULL means “unknown/missing,” not “smallest.” So MAX skips NULL values entirely. If every value in the set is NULL, the result is NULL.

What values can it compare?

• Numbers: largest numeric value, such as highest salary.
• Dates/timestamps: latest date or newest event.
• Text: some databases allow it, using collation rules, which means the database’s alphabetic sorting rules.

MAX vs. other ways to get the top value

When to use which: If you only need the largest value, MAX is the cleanest choice. If you need the whole row that owns that value, you usually need sorting, a window function, or a join back to the max result.

Performance notes

In the simplest case, MAX scans the rows once, so the time cost is O(n) for each group and the extra memory is usually O(1). In real databases, an index on the target column can make things faster: many engines can jump to the end of a b-tree index and read the highest value quickly. That is an optimizer improvement, not a promise of the SQL language, so you should think of MAX as logically scanning all rows unless the engine proves otherwise.

Edge cases interviewers like

• All values are NULL: result is NULL.
• Empty result set: aggregate without groups returns NULL.
• Multiple rows share the same maximum: MAX still returns one value; it does not tell you which row it came from.
• Text comparison: results can change with collation, so alphabetical order may differ across databases or locales.

Memory Hook: Picture a scoreboard: every new row challenges the current champion, and MAX keeps only the best score on the board.

Real-World Story: In a checkout service for an e-commerce app, the team wants the highest order total per customer to flag VIP buyers. They run MAX(order_total) grouped by customer_id to find the largest purchase amount. That number feeds a loyalty dashboard and a fraud rule that watches for unusually large transactions.

What goes wrong when someone misunderstands MAX? A developer may assume it returns the latest row, not just the largest value. Then they write code that shows the biggest payment amount but pairs it with the wrong order ID, causing support to email the wrong customer. In logs, you might see mismatched IDs and totals, and users notice weird VIP badges or incorrect alerts. The fix is to separate “find the max value” from “find the row that owns it.”

Follow-up & Tricky Questions:

• How does MAX handle NULL? It ignores NULL values. If every value is NULL or the set is empty, the result is NULL.
• Can MAX work with dates? Yes. For dates and timestamps, it returns the latest value, which is often very useful for “most recent activity” queries.
• What is the difference between MAX(col) and ORDER BY col DESC LIMIT 1? MAX returns one value, while ORDER BY ... LIMIT 1 returns a full row. Use MAX when you only need the value; use ordering when you need the row itself.
• How do you get the row that has the maximum value? Compute the max first, then join back to the table on the max value, or use a window function with ranking. If ties matter, you need a rule for which tied row to choose.
• Can MAX be used as a window function? Yes. With OVER, it gives the maximum over a moving partition while still returning one result per row, which is useful for comparisons and running analytics.
• Tricky: Does MAX return 0 for an empty table? No. Aggregate MAX returns NULL when there are no non-NULL values to compare.
• Tricky: Is MAX the same as “last inserted row”? No. It is based on value order, not insertion order. The newest inserted row may have a smaller value than older rows.
• Tricky: Does MAX tell you which row won? No. It only returns the winning value, not the row identity. If you need the row, you must query differently.

Common Mistakes:

• Thinking MAX returns a row. Correction: it returns a single value, so use a join or ranking when you need the full row.
• Assuming NULL counts as the smallest value. Correction: MAX ignores NULL entirely.
• Using MAX for “latest inserted”. Correction: “latest” by insert time needs a timestamp column, not the aggregate itself.
• Forgetting collation for text. Correction: text max depends on database sorting rules, so results can vary by locale and engine.

Memory Hook: “MAX = keep the champion.” Every new row is a challenger; the current best survives unless the challenger is bigger.

Cheat Sheet:

• MAX returns the largest non-NULL value.
• Works as an aggregate and, with OVER, as a window function.
• Time cost is usually O(n); memory is usually O(1).
• Returns NULL for empty/all-NULL input.
• Great for highest price, latest date, or top score.
• Use ordering or ranking if you need the row, not just the value.

Practice Tasks:

• Write a query to find the highest salary in an employees table.
• Group by department and return the max salary per department.
• Find the latest created_at timestamp for each customer, then think about how you would also fetch the matching order row.