FIRST_VALUE

Hook: Interviewers love FIRST_VALUE because it looks simple, but it quietly checks whether you understand ordering, partitions, and determinism.

Question: What does FIRST_VALUE do in SQL?

Answer: FIRST_VALUE is a window function that returns the value from the first row in an ordered window. You usually pair it with PARTITION BY to restart the calculation for each group and ORDER BY to define what “first” means. A key detail is that if two rows tie on the sort key, you should add a tie-breaker so the result is stable.

Interview-Ready Answer: I’d say FIRST_VALUE returns the value from the first row in a window, so it’s great when I want a per-group baseline like the top salary or first event. I usually use PARTITION BY for grouping and ORDER BY to define which row is first, and I add a tie-breaker so the result does not depend on engine row order.

What it is

FIRST_VALUE is a window function, meaning it calculates a result across a set of related rows while still returning one output row for each input row. Think of it like a “leader” label: every row in the window can see which row comes first after sorting.

How it works under the hood

1. The database forms a partition for each PARTITION BY group, or one big partition if you omit it.
2. Inside each partition, it sorts rows by the ORDER BY list. This is the most important part, because “first” only exists after sorting.
3. The engine defines a frame, which is the slice of rows visible to the current row. A frame is just the subset the function can look at.
4. FIRST_VALUE(expr) reads expr from the first row in that frame.
5. The same value is then repeated for every row whose frame starts at the same first row.

For many interview questions, the mental model is: partition first, sort second, read the first row third.

When and why to use it

• Find the first purchase date per customer.
• Show the earliest status in a workflow.
• Attach a baseline value to every row in a group, such as the highest salary in a department.
• Compare later rows against the first row without writing a self-join.

Comparison with similar tools

Performance and complexity

The sort usually dominates the cost. A good mental estimate is O(n log n) time because the rows must be ordered, with extra memory for sorting. The function itself is cheap after the sort, but large partitions can spill to disk if the engine does not have enough memory. In PostgreSQL-style systems, that can happen when sort memory is only a few MB and the partition is large or wide.

Important edge cases

• No ORDER BY: the result is not meaningful for business logic because “first” has no clear definition.
• Ties: if two rows have the same sort key, add another key such as an id column.
• NULLs: FIRST_VALUE returns the first row’s value even if it is NULL; it does not automatically mean “first non-null.”
• Frame choice: the default frame is often fine, but an explicit frame makes intent clearer and helps you avoid surprises when learning or reviewing queries.

Rule of thumb: if you can explain which row is first in one sentence, FIRST_VALUE is the right tool.

Real-world story

Imagine an e-commerce checkout service that tracks the first marketing channel for each customer. The analytics team uses FIRST_VALUE(channel) OVER (PARTITION BY customer_id ORDER BY event_time) to label each customer with the channel that brought them in.

One day, the team notices the dashboard keeps shifting: “organic” is 4% higher in the morning, then “ads” wins after the nightly batch. The bug turns out to be ties in event_time. Two events arrive with the same timestamp, and without a tie-breaker like event_id, the engine is free to choose either row first.

What goes wrong: reports become unstable, finance questions attribution numbers, and support sees tickets about “changing totals.” Logs show the same customer row getting a different first channel after reloads or reprocessing. The fix is simple: make the ordering deterministic and, if needed, add a second sort key or pre-clean the data.

Follow-up & Tricky Questions:

• What is the difference between FIRST_VALUE and MIN? FIRST_VALUE follows the row order you define, while MIN returns the smallest value regardless of row order. If you want the value from the earliest or highest-ranked row, use FIRST_VALUE; if you only want the smallest scalar, use MIN.
• How does PARTITION BY change the result? It splits the data into independent groups, so the first value is calculated separately for each group. Without it, the whole result set is treated as one partition.
• Why would you add an explicit frame clause? It makes the query easier to read and removes ambiguity for learners and reviewers. For FIRST_VALUE, it usually matches the intended behavior of showing the first row for the entire partition.
• How do NULLs affect FIRST_VALUE? It returns the value from the first ordered row even if that value is NULL. If you need the first non-null value, you must use a different pattern or a database-specific feature.
• Is the result deterministic without a unique ORDER BY? No. If multiple rows tie, the engine may choose any peer row first unless you add a tie-breaker.
• Does FIRST_VALUE return the first row in the table? No, it returns the first row in the window order you define. Table storage order is not the same as logical SQL order.
• Is FIRST_VALUE the same as “first non-null value”? No. It is about the first sorted row, not about skipping nulls. Many candidates miss this distinction.

Common Mistakes:

• Forgetting ORDER BY: fix it by defining a real business order, such as timestamp, score, or amount.
• Not breaking ties: fix it by adding a unique column like id or created_at, id.
• Assuming it skips NULLs: fix it by explicitly handling NULLs in the sort key or using a different technique.
• Confusing it with MIN: fix it by remembering that FIRST_VALUE is order-based, not value-based.

Memory Hook: “Partition, sort, spotlight.” First split the group, then sort it, then shine the spotlight on the first row.

Cheat Sheet:

• FIRST_VALUE is a window function, not an aggregate.
• PARTITION BY defines the group.
• ORDER BY defines what “first” means.
• Add a tie-breaker for deterministic results.
• It returns the first ordered row’s value, not the smallest value.
• Sorting cost usually dominates performance.

Practice Tasks:

• Return the first order date per customer from an orders table.
• Show the first status change per ticket in a support system.
• Rewrite a query that uses MIN incorrectly and replace it with FIRST_VALUE plus a deterministic order.