The reading room: what an answer is measured in

The extreme value: argMax without a window

18 min

What you'll learn

answer the question "what was there at the maximum" with a single : argMax and argMin
return a pair of values at once: argMax((a, b), t)
show what extra work the window version adds for the same answer, and find that work in the plan
tell argMax, any, anyLast and topK apart, and know which one does not promise a specific value

A question at the issue window

A visitor with card No. 1 comes up to the window: “I was here last week and opened something about navigation — what was it?” The reading-room log contains every card opening from March. You need to find this visitor’s latest opening.

The first solution is obvious: sort the visitor’s records by time and take the top one. In the reading room, you can do that with a — number the records within each visitor and keep the first. The answer will be correct.

But that method has a price. The reading room has 23 565 records and 791 visitors with openings. To find one latest record per visitor, the engine has to sort everything it read by time within each group.

So the task is not just to get the right answer. You need to check whether the same latest record can be returned without a full sort.

ClickHouse has a regular for that.

The issue window in the evening: through the brass lattice a visitor's card is lifted with one record glowing on it, and behind it a dimmed ribbon of earlier records recedes into the frame; along the right edge the same ribbon lies unrolled, sorted and numbered, with an arrow leading from it to that very card. — The answer is the same, but the right-hand route had to unroll the whole ribbon first and the left-hand one did not.

On the left is the answer using argMax, on the right using a window function. Both methods find the latest record for each visitor; the first row answers the visitor at the issue window. The last column shows how many of the 791 visitors produced different results.

The value at the maximum

argMax(value_to_return, value_to_maximize) does not answer “what is the maximum?” It answers “what was stored next to the maximum?” We’ll call the second argument the comparison value. The engine finds its greatest value and returns the first argument from the same row. argMin works the same way, but looks for the smallest comparison value.

For the question at the issue window, that is exactly what you need: time determines the latest record, and the title from that record becomes the answer. There is no need to sort the whole group — one state per group is enough.

The first argument can be a pair or a triple. A set of values in parentheses is called a tuple; the returns the whole tuple from the selected row.

In the cell, the “title and hall” column shows both values FROM THE SAME latest record. That detail matters: two separate argMax calls over different columns can choose different rows when there is a tie.

A tie on the comparison value

Now consider the edge case. What does argMax return if the maximum value of the second argument appears twice?

Any matching value. ClickHouse does not promise which one. There are no ties in the reading room: all 791 visitors have a unique latest opening, so the disagreement column shows 0.

If ties are possible, you need to define the selection rule explicitly. argMax(x, (t, id)) compares the pair, so when the times are equal, the larger id wins — making the answer deterministic.

Three neighbors that are easy to confuse

There are several functions near argMax that may look suitable but answer different questions.

any(x) — “any value from the group.” Not the first and not the last: the engine reads parts in parallel and returns whichever value it encounters first. Two runs over the same data may return different answers, and that is not a defect — it is exactly what the function promises.
anyLast(x) — “the last value READ,” not the latest by time. Read order has nothing to do with your time column.
topK(n)(x) — the n most frequent values, not the latest ones. That answers a completely different question.

Of these four, only argMax ties the returned value to an explicitly specified comparison value. So when the question is “what was there at the maximum?”, that is the function to choose.

One more distinction: argMax already appeared as a DEDUPLICATION method — “keep the latest version of a row” — in the chapter on columnar storage. The task here is different: you are not removing duplicates, but answering a question about an extreme value. There are no duplicates in the reading room, but someone asks for the latest opening every day.

QUERY: The window version sorted the whole month for the sake of one row. Correct answer. Now look at the plan and count the price of that neatness.

Both methods read the same 23 565 rows and return the same answer. The difference starts after reading: argMax aggregates, while the window version adds a sort. So here you need to compare their price in the plan. The table-read row has been removed from the plan because it contains the database name, which is different for every run.

Both methods read 23 565 rows, but their paths diverge after the read: argMax keeps one state per group and takes four plan steps, while the window version adds a sort, bringing the total to six steps.

Interview question

How this comes up in interviews

A common version is: “How would you get the latest value for each user?” A window-function solution with numbering will be accepted. But the usual follow-up is whether you can solve it without a — and the expected answer is argMax.

You should know both methods. The window function gives the correct answer, but requires extra work after reading. argMax solves the same problem with regular .

The second question is: “How is argMax different from any?” The short answer: any does not promise which value it will return; it guarantees only that the value comes from the group. You should not build a report on it if that report will later be reconciled.

The third question checks the edge case: “How do you make the answer deterministic when timestamps are equal?” Compare a tuple and include a unique column in it.

Check yourself

You need to return the title and hall FROM ONE AND THE SAME latest record for a visitor. Which option guarantees that?

Practice: solve the tasks

Solved 0 of 2 · any 1 is enough to pass

Key takeaways

question	method	price
What did the visitor open most recently?	`argMax(title_id, event_ts)`	23 565 rows, 4 plan steps, no sort
Same question	with numbering	23 565 rows, 6 plan steps, one sort

There is no difference in reading: both methods scan the same 23 565 rows. The difference starts AFTER the read. The window version still has to sort the records, while argMax keeps the extreme value as part of .

That gives us a rule we’ll use again in the chapter on layout: data read is the first measure of price, but not the only one. If the number of rows read is the same, inspect the plan and see what the engine does next.

And the visitor at the issue window has an answer: title 117, north hall, March 25.