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()andMAX(). - Compare dates and strings alphabetically.
- Group min/max limits by category.
Detailed Concept Explanation
MIN(col): Returns the minimum value, ignoringNULLs.MAX(col): Returns the maximum value, ignoringNULLs.
Non-Numeric Values
- Dates:
MINreturns the earliest date, whileMAXreturns the latest date. - Strings:
MINandMAXreturn 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
MINandMAXare indexed. Indexes store values in order, allowing the database to instantly retrieve boundary values without scanning the entire table.
Interview Perspective
Note
Interview Question: Can MIN and MAX be used on string columns?
Yes. When applied to strings, MIN returns the alphabetically first value (e.g. 'Apple') and MAX returns the alphabetically last value (e.g. 'Zebra') based on the database's collation settings.
Interactive Challenges
Summary
MIN and MAX find boundary limits for numbers, dates, and strings. Index these columns to keep searches fast.
DataPlayArena Technical Editorial Board
Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.
Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.