COUNT, SUM, and AVG tell you how many, how much, and what's typical. But sometimes the question is simpler: what's the smallest value? The largest? And more importantly, which row contains it?

That last question, finding the full row behind an extreme value, is one of the most common SQL interview patterns.

Insert the following rows to follow along:

MIN returns the smallest value in a column. MAX returns the largest. They work just like you'd expect with numbers.

Example: What's the shortest and longest track in the catalog?

The shortest track is 90 seconds (Petals) and the longest is 420 seconds (Drop Zone). Like other aggregate functions, MIN and MAX collapse all rows into a single result row.

You can combine them with other aggregates in the same query:

The range (the gap between the shortest and longest track) gives a quick sense of how varied the data is. In this case, it's 330 seconds, meaning the longest track is more than four times the length of the shortest.

MIN and MAX Skip NULLs

As covered in the previous chapter, all aggregate functions skip NULL values. MIN and MAX are no exception. If a column has values 10, NULL, 30, and 20, MIN returns 10 and MAX returns 30. The NULL is ignored entirely.

This matters with columns like streams.duration_listened, which could theoretically be NULL for a stream that failed to record its duration. MIN and MAX would simply skip those rows rather than returning NULL or raising an error.

When no rows match a filter, MIN and MAX return NULL, just like SUM and AVG:

No streams come from Australia in the sample data, so this returns NULL. Use COALESCE if you need a default value instead.

DISTINCT with MIN and MAX

You can write MIN(DISTINCT column) or MAX(DISTINCT column), and it's valid SQL. But it's pointless. Removing duplicates before finding the minimum or maximum doesn't change the result. The smallest value among [10, 20, 20, 30] is 10 whether you deduplicate first or not. This is different from COUNT and AVG, where DISTINCT actually changes the result.

MIN and MAX aren't limited to numbers. They work on any data type that has a natural ordering, including strings.

With strings, MIN and MAX use lexicographic (dictionary) ordering. MIN returns the string that sorts first, and MAX returns the string that sorts last.

This returns alice_m (sorts first) and jake_us (sorts last) based on alphabetical ordering of the username column.

Case Sensitivity and Collation

Lexicographic ordering for strings depends on the database's collation setting. In most default PostgreSQL collations, uppercase letters sort before lowercase letters. So 'B' comes before 'a' in a case-sensitive sort.

The StreamFlow dataset uses lowercase usernames, so this isn't an issue here. But when working with mixed-case data, the results of MIN and MAX on strings can be surprising. If you need case-insensitive behavior, apply LOWER first:

This normalizes to lowercase before comparing, so 'The Midnight' is compared as 'the midnight'. That puts it after 'neon dreams' in sort order rather than before.

When String MIN/MAX is Useful

Finding alphabetical boundaries might seem academic, but string MIN and MAX have practical uses. For example, finding the earliest or latest value in a column that stores sortable codes, version strings, or identifiers:

Country codes are two-letter strings that sort predictably. This returns 'BR' and 'US', the alphabetically first and last country codes among users.

Dates and timestamps have a natural chronological order, so MIN gives you the earliest date and MAX gives you the latest. This is one of the most common uses of MIN and MAX in practice.

Example: When did the earliest and most recent user sign up?

The first user signed up on 2020-03-30 (hiro_jp) and the most recent on 2025-01-15 (jake_us).

The same works with timestamps.

Example: When was the first and last stream recorded?

Combining with WHERE

MIN and MAX with dates become powerful when combined with filters.

Example: When was the first stream from the UK?

When was the last payment made by user alice_m?

These queries answer the kind of questions that come up constantly in analytics: "when did X first happen?" and "when did X last happen?"

Here's where it gets interesting. MIN and MAX tell you the extreme value, but they don't tell you which row it came from. If you ask "what's the longest track?", MAX(duration_seconds) gives you 420. But which track is it? Who's the artist? What album is it on?

This is the "row with the extreme value" problem, and it shows up in interviews all the time:

The challenge is that you can't just add more columns to the SELECT alongside an aggregate:

What goes wrong here?

PostgreSQL returns: ERROR: column "tracks.title" must appear in the GROUP BY clause or be used in an aggregate function.

MAX collapses all rows into one, but title has 10 different values. The database can't pick which title to show. You need a different approach.

Approach 1: Subquery in WHERE

The most straightforward approach uses a subquery to find the extreme value, then filters for the row that matches it:

This works in two steps. The inner query finds the maximum duration (420). The outer query finds all tracks with that duration. The logic is clean and readable.

The same pattern works for finding the highest single payment:

The subquery finds the max amount (14.99), and the outer query returns every payment with that amount, along with the username.

Approach 2: ORDER BY with LIMIT

Instead of finding the max value first and then matching, you can sort by the column and take the first row:

This is concise and works well when you want exactly one row. For the highest payment:

The downside is that LIMIT 1 returns only one row even if there are ties. If two payments share the highest amount, this approach silently drops one of them. The subquery approach returns all ties.

Approach 3: Correlated Subquery with NOT EXISTS

A third approach uses NOT EXISTS to find the row where no other row has a larger value:

This reads as: "find every track where no other track has a longer duration." If multiple tracks tie for the longest, they all appear in the result, just like the subquery-in-WHERE approach.

This approach is more verbose, and for simple cases, the subquery-in-WHERE version is clearer. But NOT EXISTS becomes useful in more complex scenarios where the comparison involves multiple columns or conditions.

Comparing the Three Approaches

For interviews, the subquery-in-WHERE approach is the safest default. It's clear, handles ties correctly, and works across all databases.

Handling Ties Explicitly

When multiple rows share the extreme value, you need to decide how to handle them. The sample data has three payments with amount 14.99 (payment_ids 4, 5, and 10). The subquery-in-WHERE approach returns all three.

If you want just one row when there are ties, you need a tiebreaker. Sort by a secondary column:

This finds all payments with the maximum amount, then picks the most recent one. Choosing a meaningful tiebreaker (most recent, lowest ID, alphabetical) is better than returning an arbitrary row.

Finding the Row with the Minimum Value

Everything above applies to MIN as well. Just flip the direction. Find the shortest track:

Or using ORDER BY:

So far, every query has found the global minimum or maximum across an entire table. But real questions are often scoped: "What's the longest track on each album?" or "What's the most recent stream for each user?"

These are per-group extremes, and they need GROUP BY, which the next chapter covers in detail. But the concept is worth previewing because it builds directly on MIN and MAX.

Without GROUP BY, answering "what's the longest track on each album?" requires separate queries:

This is tedious and doesn't scale. The next chapter introduces a single query that handles all groups at once:

GROUP BY splits the rows into groups (one per album), and MAX runs independently within each group. One query, all albums.

But even with GROUP BY, you hit the same problem from earlier: this gives you the longest duration per album, but not which track it is. Solving "find the longest track on each album" with full row details requires techniques from later chapters (subqueries or window functions). For now, just note that the per-group version of the "row with extreme value" problem exists, and it's a common interview question.