Finding the Maximum Numeric Value

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

-- Find the highest view count achieved by any video on TokTuk
SELECT MAX(views) AS highest_video_views
FROM Videos;

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

Finding the Latest Date/Timestamp

MAX is very useful for finding the most recent date or time in a dataset.

-- Find the date the last user joined the platform
SELECT MAX(join_date) AS latest_signup_date
FROM Users;

-- Find the timestamp of the most recent interaction
SELECT MAX(timestamp) AS most_recent_interaction
FROM Interactions;

Result: The latest date or timestamp value present in the respective column.

Finding the Maximum String Value

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

-- Find the username that comes last alphabetically
SELECT MAX(username) AS last_alphabetical_username
FROM Users;

Result: The username like "ZebraUser" would typically be returned over "AppleUser". Case sensitivity depends on the database collation settings.

MAX and NULL Value Handling

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

-- MAX ignores NULLs
-- If views are [100, 500, NULL, 200], MAX(views) returns 500.
SELECT MAX(views) AS max_views
FROM Videos;

Method 1: Using a Subquery in the WHERE Clause

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

-- Find the video(s) with the highest view count
SELECT video_id, title, user_id, views
FROM Videos
WHERE views = (SELECT MAX(views) FROM Videos);

This returns all columns for any video whose views match the overall maximum. If multiple videos tie for the highest view count, all of them will be returned.

Method 2: Using Window Functions (Often More Efficient)

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

-- Find the most viewed video(s) using RANK
WITH RankedVideos AS (
  SELECT
    video_id, title, user_id, views,
    RANK() OVER (ORDER BY views DESC) as rnk -- Assign rank based on views
  FROM Videos
)
SELECT video_id, title, user_id, views
FROM RankedVideos
WHERE rnk = 1; -- Select rows with the top rank (rank 1)

This approach can be more efficient, especially if you need the top N records, and avoids calculating MAX 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 Top 1)

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

-- Find *one* video with the highest view count
SELECT video_id, title, user_id, views
FROM Videos
ORDER BY views DESC -- Sort by views descending
LIMIT 1; -- Take only the first row

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

Finding Maximums within Categories

Determine the peak value for each group.

-- Find the highest view count for videos uploaded by each user
SELECT
  user_id,
  MAX(views) AS highest_views_for_user
FROM Videos
GROUP BY user_id -- Group rows by creator
ORDER BY highest_views_for_user DESC;

This shows the best performance achieved by each individual user, regardless of their average performance.

Finding the Latest Event per Category

Identify the most recent occurrence within each group.

-- Find the most recent interaction timestamp for each video
SELECT
  video_id,
  MAX(timestamp) AS latest_interaction_time
FROM Interactions
GROUP BY video_id
ORDER BY latest_interaction_time DESC;

This helps identify which videos have the most recent engagement activity.

Combining MAX with Other Aggregates per Group

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

-- Show each user's best, worst, and average video views
SELECT
  u.username,
  COUNT(v.video_id) AS video_count,
  MAX(v.views) AS max_views,
  MIN(v.views) AS min_views,
  AVG(v.views) AS avg_views
FROM Users u
JOIN Videos v ON u.user_id = v.user_id
GROUP BY u.user_id, u.username
ORDER BY max_views DESC;

This provides a performance range (max vs. min) and central tendency (avg) for each user's video portfolio.

Filtering Groups Based on MAX (HAVING Clause)

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

-- Find users whose single best video has over 50,000 views
SELECT
  u.username,
  MAX(v.views) AS max_views
FROM Users u
JOIN Videos v ON u.user_id = v.user_id
GROUP BY u.user_id, u.username
HAVING MAX(v.views) > 50000 -- Filter groups based on the max value found
ORDER BY max_views DESC;

Finding the Nth Maximum Value

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

-- Find the 2nd highest view count using subquery
SELECT MAX(views)
FROM Videos
WHERE views < (SELECT MAX(views) FROM Videos);

-- Find the 3rd highest view count using window functions (more general)
WITH RankedViews AS (
  SELECT
    views,
    DENSE_RANK() OVER (ORDER BY views DESC) as rnk
  FROM Videos
)
SELECT DISTINCT views
FROM RankedViews
WHERE rnk = 3;

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

Finding the Row with MAX Value per Group (Greatest-N-Per-Group)

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

-- Find details of each user's most viewed video using ROW_NUMBER()
WITH RankedUserVideos AS (
  SELECT
    v.video_id, v.title, v.user_id, v.views, v.upload_date,
    ROW_NUMBER() OVER (PARTITION BY v.user_id ORDER BY v.views DESC, v.video_id DESC) as rn
    -- Partition by user, order by views descending. Tie-break with video_id if needed.
  FROM Videos v
)
SELECT ruv.video_id, u.username, ruv.title, ruv.views, ruv.upload_date
FROM RankedUserVideos ruv
JOIN Users u ON ruv.user_id = u.user_id
WHERE ruv.rn = 1; -- Select the top-ranked video (rn=1) for each user

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