MIN & MAX Functions

Overview

The MIN and MAX functions identify the smallest and largest values in a column or partition.

Learning Objectives

• Find numerical limits using MIN() and MAX().
• Compare dates and strings alphabetically.
• Group min/max limits by category.

Detailed Concept Explanation

• MIN(col): Returns the minimum value, ignoring NULLs.
• MAX(col): Returns the maximum value, ignoring NULLs.

Non-Numeric Values

• Dates: MIN returns the earliest date, while MAX returns the latest date.
• Strings: MIN and MAX return the first and last alphabetical values based on collation rules.

Code Examples

SQL

-- Find price extremes and date limits in a single scan
SELECT 
  MIN(price) AS cheapest_item,
  MAX(price) AS most_expensive_item,
  MIN(created_at) AS first_log_date,
  MAX(created_at) AS latest_log_date
FROM inventory;

Best Practices

• Use Indexing: Ensure columns queried with MIN and MAX are indexed. Indexes store values in order, allowing the database to instantly retrieve boundary values without scanning the entire table.

Interview Perspective

Interactive Challenges

Summary

MIN and MAX find boundary limits for numbers, dates, and strings. Index these columns to keep searches fast.