SQL / AGGREGATION WITH GROUP BY
MIN and MAX beyond numbers
Apply MIN and MAX to text, dates and timestamps, read their ordering rules correctly, and fetch the actual row behind a MAX instead of mismatched columns.
What you will learn
- Use MIN and MAX on text, dates and timestamps; they follow the order ORDER BY uses
- Read text results lexicographically: 'BUG-10' sorts before 'BUG-2' and 'BUG-9'
- Remember MIN/MAX skip NULLs and return NULL only if the whole group is NULL
- Get the row behind a MAX with a subquery filter, not by adding columns to SELECT
Understanding MIN and MAX beyond numbers
MIN and MAX are the only common aggregates that do no arithmetic. All they need is a comparison operator, so every type the database knows how to sort qualifies: text, date, timestamp, interval, and enum types, where the order is the order the labels were declared in. That makes MIN(x) exactly the first value ORDER BY x would hand you and MAX(x) the last, with NULLs discarded before any comparison happens, so a group whose values are all NULL returns NULL rather than zero or an error.
For text the ordering is lexicographic under a collation: the comparison walks character by character and stops at the first difference, and a string that is a prefix of another sorts before it. That is why MIN over ticket codes returns 'BUG-10' rather than 'BUG-2' — the first difference is the character after the hyphen, and '1' precedes '2'. MIN is not "the shortest string" and MAX is not "the largest number", because those are properties of the values, not of the ordering. The collation also decides how case is weighted, so the same query can answer 'Banana' on one server and 'apple' on another.
The second surprise is structural: every aggregate in the select list is computed independently over the whole group. MAX(opened_on) scans the dates while MIN(code) scans the codes, and nothing ties the two winners to the same source row, so the output row can describe a ticket that never existed. When you want the code of the latest ticket, compute the MAX per group first and then filter the base rows back down to it — an aggregate collapses a column, not a row.
CREATE TABLE tickets (
id integer,
project text,
code text,
opened_on date,
closed_on date
);
INSERT INTO tickets VALUES
(1, 'apollo', 'BUG-9', DATE '2024-03-14', DATE '2024-03-20'),
(2, 'apollo', 'BUG-10', DATE '2024-01-05', NULL),
(3, 'apollo', 'BUG-2', DATE '2024-05-30', DATE '2024-06-02'),
(4, 'hermes', 'TASK-7', DATE '2024-02-11', NULL),
(5, 'hermes', 'TASK-71', DATE '2024-02-11', NULL);
SELECT project,
MIN(code) AS min_code,
MAX(code) AS max_code,
MIN(opened_on) AS first_opened,
MAX(opened_on) AS last_opened,
MAX(closed_on) AS last_closed
FROM tickets
GROUP BY project
ORDER BY project;MIN and MAX need only an ordering rather than numbers, and each collapses its own column independently, so they never return a row.
Worked examples
The two answers come from different rows
Shows that MIN on one column and MAX on another can describe two unrelated tickets.
WITH tickets(code, opened_on) AS (
VALUES ('BUG-9', DATE '2024-03-14'),
('BUG-10', DATE '2024-01-05'),
('BUG-2', DATE '2024-05-30')
)
SELECT MIN(code) AS min_code,
MAX(opened_on) AS last_opened
FROM tickets;Example explained
Line 1MIN(code) compares the three strings and stops at the character after the hyphen, so 'BUG-10' wins.
Line 2MAX(opened_on) compares only the dates and returns 2024-05-30, which belongs to BUG-2.
Line 3The single output row therefore pairs a code from one row with a date from another; no ticket has both values.
Line 4Nothing in the query is wrong syntactically, which is exactly why this mistake survives code review.
Getting the row that owns the MAX
Uses the grouped MAX in a subquery to bring back the real row behind the latest date.
WITH tickets(project, code, opened_on) AS (
VALUES ('apollo', 'BUG-9', DATE '2024-03-14'),
('apollo', 'BUG-10', DATE '2024-01-05'),
('apollo', 'BUG-2', DATE '2024-05-30'),
('hermes', 'TASK-7', DATE '2024-02-11')
)
SELECT project, code, opened_on
FROM tickets
WHERE (project, opened_on) IN (
SELECT project, MAX(opened_on)
FROM tickets
GROUP BY project
)
ORDER BY project;Example explained
Line 1The subquery reduces the data to one (project, latest date) pair per project.
Line 2The row constructor (project, opened_on) compares both columns together, so a project can only match its own maximum.
Line 3The outer query reads unaggregated rows, which is what guarantees code and opened_on belong to the same ticket.
Line 4If two tickets in a project shared the maximum date, both rows would be returned rather than one being picked silently.
Collation decides text MIN and MAX
Demonstrates that the comparison rule, not the data, determines which string is smallest.
WITH names(name) AS (
VALUES ('apple'), ('Banana'), ('cherry')
)
SELECT MIN(name COLLATE "C") AS c_min,
MAX(name COLLATE "C") AS c_max,
MIN(LOWER(name)) AS lower_min
FROM names;Example explained
Line 1COLLATE "C" forces byte-order comparison, where every uppercase letter precedes every lowercase one, so 'Banana' takes MIN.
Line 2The collation changes the comparison inside the aggregate only; the value returned is still the stored spelling with its capital B.
Line 3Under a locale collation such as en_US.UTF-8 the same three rows normally give 'apple' as MIN, so the result depends on the server.
Line 4MIN(LOWER(name)) fixes the ordering but returns the folded text, which is no longer usable as a display value.
Important notes
PostgreSQL has no MIN or MAX for boolean — use bool_and and bool_or; MySQL stores booleans as tinyint, so MIN there returns 0 or 1. The pattern does not port.
MIN and MAX on dates kept as text work only when the text is ISO-8601, because that format sorts chronologically; on '03/14/2024' style values they return the alphabetically first month instead.
Common mistakes
Reading MAX on a text version or code column as the biggest number: with '1.9.0' and '1.10.0' stored, MAX returns '1.9.0', so a "latest release" panel points at the older row forever.
Placing a bare column next to MAX(created_at) and assuming they belong together: PostgreSQL rejects the query outright, while MySQL with ONLY_FULL_GROUP_BY disabled returns an arbitrary row's value that looks right in a five-row test table.
Expecting text MIN and MAX to ignore case: under a binary collation MIN flips from 'apple' to 'Banana', so results change when the query moves to a server with a different collation.
Try it yourself
Change, predict, then run
Create a releases table with product text, version text ('1.2.0', '1.9.0', '1.10.0') and released_on date, then write one grouped query returning MIN(version), MAX(version), MIN(released_on) and MAX(released_on) per product. Write a second query that returns the version actually released last, and compare it with MAX(version).
Open the SQL workspaceCheck your understanding
A logins table has one row per login with user_id, device (text) and login_at (timestamp). User 7's earliest login came from 'ipad', a later one from 'android', and the most recent from 'pixel'. What does MIN(device) return for user 7 in SELECT user_id, MIN(device), MAX(login_at) FROM logins GROUP BY user_id?
- 'ipad', the device of the earliest login
- 'pixel', the device of the login found by MAX(login_at)
- 'android', the first device name in collation order, unrelated to any timestamp
- An arbitrary one of the three devices, since the engine may pick any row in the group
Show answer
MIN(device) compares the device strings themselves and returns 'android'; the timestamps take no part in that comparison. Option 0 is tempting because MIN(login_at) really would give the earliest login, but applying MIN to a different column asks a different question, and the two aggregates are evaluated independently over the same group. The result is also deterministic rather than arbitrary — only the collation used to compare the text can change it.