MAX

MAX returns the maximum non-null value in a set.

It is useful for latest timestamps, highest scores, and top prices.

Core Concepts

flowchart TD
  A[Input values] --> B[Compare values]
  B --> C[Return largest non-NULL value]

Use case	Example
Largest sale	MAX(total)
Latest date	MAX(order_date)
Highest score	MAX(score)

Code Examples

-- Setup table for max examples.
CREATE TABLE leaderboard_max (
  player TEXT NOT NULL,
  score  INTEGER
);

INSERT INTO leaderboard_max (player, score)
VALUES
  ('Ava', 78),
  ('Ben', 92),
  ('Chen', 85);

Expected output
Table created and three score rows inserted.

-- Highest score.
SELECT MAX(score) AS top_score
FROM leaderboard_max;

top_score
92

-- Player(s) who reached the top score.
SELECT player, score
FROM leaderboard_max
WHERE score = (SELECT MAX(score) FROM leaderboard_max);

player	score
Ben	92

SQLite-Specific Nuances

SQLite Nuance

Like other aggregates, MAX() ignores NULL values in SQLite.

Common Pitfalls / Best Practices

Pitfall

Using MAX(id) as a proxy for "most recent" without confirming id ordering truly maps to time.

Best Practice

Use explicit timestamp columns for recency logic; reserve MAX(id) for identity-related checks only.

Quick Challenge

Return the highest score and matching player from leaderboard_max.

View Solution

SELECT player, score
FROM leaderboard_max
WHERE score = (SELECT MAX(score) FROM leaderboard_max);

player	score
Ben	92