Finding the Minimum Numeric Value

The most straightforward use is finding the smallest number in a column.

Result: A single value representing the lowest view count across all videos.

Finding the Earliest Date/Timestamp

MIN is very useful for finding the earliest date or time in a dataset.

Result: The earliest date or timestamp value present in the respective column. In our data, the first user joined on January 1st, 2023.

Finding the Minimum String Value

When used with text or string columns, MIN returns the value that comes first in alphabetical (or dictionary) order, according to the database's collation rules.

Result: The username "alice" is returned as it comes first alphabetically among all usernames in our dataset.

MIN and NULL Value Handling

Similar to SUM and AVG, the MIN function ignores NULL values when determining the minimum. If all values considered are NULL, MIN returns NULL.

Method 1: Using a Subquery in the WHERE Clause

This is the most common and intuitive method. First, find the minimum value using a subquery, then select rows where the column equals that minimum value.

This returns all columns for the video with the lowest views (500), which is the "Cooking Tips" video by user 1 (alice).

Method 2 [Advanced]: Using Window Functions

Window functions like RANK() or DENSE_RANK() can identify the bottom record(s) without a separate subquery for the MIN value.

This approach can be more efficient, especially if you need the bottom N records, and avoids calculating MIN separately. RANK() handles ties by giving them the same rank and skipping subsequent ranks; DENSE_RANK() also gives ties the same rank but doesn't skip ranks.

Method 3: Using ORDER BY and LIMIT (Simple for Bottom 1)

If you only need one row containing the minimum value (and don't care about ties), sorting and limiting is the simplest way.

Note: If there's a tie for the minimum value, this method arbitrarily picks only one of the bottom rows. Use methods 1 or 2 if you need all tied rows.

Finding Minimums within Categories

Determine the lowest value for each group. For example, this query shows the lowest performing video for each user who has uploaded content.

Finding the Earliest Event per Category

Identify the first occurrence within each group. In this query, we are identifying when each video first received engagement activity.

Combining MIN with Other Aggregates per Group

Get a fuller picture by calculating multiple aggregate metrics for each group.

This provides a complete performance overview for each content creator, showing their video count and view statistics.

Filtering Groups Based on MIN (HAVING Clause)

Select only groups where the minimum value meets a certain condition.

Finding the Nth Minimum Value

Finding values other than the absolute minimum often involves window functions or clever subqueries.

The window function approach using DENSE_RANK() is generally more flexible for finding the Nth value and handles ties gracefully.

Finding the Row with MIN Value per Group (Least-N-Per-Group)

This is a common, slightly complex task: retrieve the full row corresponding to the minimum value within each group (e.g., find each user's least viewed video details). Window functions are often the most efficient solution.

ROW_NUMBER() assigns a unique number within each partition. Using PARTITION BY user_id and ORDER BY views ASC ensures rn = 1 corresponds to the lowest-view video for that user. Other methods involving subqueries or lateral joins exist but can be less performant.

Using ELSE for Defaults and NULL Handling

The optional ELSE clause provides a default value if none of the WHEN conditions are met. If ELSE is omitted and no condition matches, the CASE expression returns NULL.

This categorizes videos based on their view count, with an ELSE clause to handle any videos with 0 or NULL views.