easy Main Office Assignment for Each Employee

Number every office a person holds within their own partition, sorting main_flag descending so the lexical fact that 'Y' > 'N' floats the flagged main office to rank 1 — then keep only rank 1; a one-office person trivially has rank 1 because the partition has a single row, so the "single office is automatically main" rule needs no special case.

Problem

Table OfficeAssignment(employee_id, office_id, main_flag), where main_flag is an ENUM of 'Y' / 'N' and (employee_id, office_id) is the primary key. A person can be assigned to several offices but designates at most one as main ('Y'). If a person has exactly one office, that office is their main office no matter what its flag says. Report each employee with their one main office, ordered by employee_id.

The two insights the problem hinges on

This looks like it needs branching logic — "if multiple offices pick the 'Y'; else pick the only one" — but both branches collapse into a single ranking. Two facts make that work:

1. 'Y' > 'N' lexically. main_flag is a string, so ORDER BY main_flag DESC is a string comparison, not a flag comparison. In ASCII/Unicode and every common collation, 'Y' (code 89) sorts after 'N' (code 78), so descending puts 'Y' first. The row that gets rn = 1 in a multi-office partition is exactly the one flagged main. We are not telling the database what "main" means — we are exploiting that the sort order of the two literal strings already encodes it.
2. A one-office partition has nothing to break ties on. ROW_NUMBER() hands out 1, 2, 3, … per partition. If the partition holds a single row, that row gets 1 regardless of whether its flag is 'Y' or 'N'. So employee 1 (office 101, flag 'N') survives the rn = 1 filter without any OR has-only-one-office clause. The special case is not handled — it is absent, because the window function's per-partition counting already covers it.

The query

SELECT employee_id, office_id
FROM (
    SELECT employee_id, office_id,
           ROW_NUMBER() OVER (
               PARTITION BY employee_id
               ORDER BY main_flag DESC
           ) AS rn
    FROM OfficeAssignment
) AS ranked
WHERE rn = 1
ORDER BY employee_id;

It is one logical step, not two: the inner query stamps a rank; the outer WHERE rn = 1 is the actual selection of the main office; ORDER BY employee_id is only presentation. (You cannot filter on rn in the same SELECT that defines it — window functions are evaluated after WHERE — which is why the rank lives in a subquery / CTE and the filter wraps it.)

Traced on the sample data

The window function evaluates partition by partition. Inside each partition the rows are sorted by main_flag DESC, then numbered top-down.

After the filter and ORDER BY employee_id:

employee_id | office_id
-----------+----------
    1      |   101
    2      |   101
    3      |   103
    4      |   103

Note employees 2 and 4 both resolve to office 103 vs 101 by flag, not by office number; employees 1 and 3 keep their only office despite it being flagged 'N'. The branch you would have hand-written never appears.

Why the naive approaches go wrong

Filtering on the flag directly

-- WRONG: drops every single-office employee whose flag is 'N'
SELECT employee_id, office_id
FROM OfficeAssignment
WHERE main_flag = 'Y';

This returns only employees 2 and 4. Employees 1 and 3 vanish because their lone office is flagged 'N' — yet the spec says a single office is the main one. The flag alone does not encode the rule; the rule is "the flagged office if one exists, otherwise the only office." Ranking captures both; a flat WHERE captures only the first half.

The non-deterministic tie-break in the ranking solution itself

If an employee has multiple offices and none is flagged 'Y' (e.g. two rows both 'N'), all rows in that partition tie on main_flag. ROW_NUMBER() still must assign distinct ranks, so it picks one row arbitrarily — the winner depends on the engine's physical row order and can change between runs, after a re-index, or across PostgreSQL / MySQL / SQL Server. The sample data never triggers this, so the bug hides. Add a deterministic secondary key:

ROW_NUMBER() OVER (
    PARTITION BY employee_id
    ORDER BY main_flag DESC, office_id
)

Now ties resolve to the lowest office_id — reproducible everywhere. (The original problem leaves this case undefined; in production you would clarify the rule rather than let the optimizer choose.)

Pitfalls

• Assuming 'Y' > 'N' is a logical fact. It is a collation fact about two strings. Under almost any collation it holds, but if someone changes the flag vocabulary (say 'Main' / 'Branch', where 'B' < 'M' so DESC would now float 'Main' correctly, but 'Primary' / 'Secondary' would break it), the silent dependence on alphabetical order becomes a bug. Prefer an explicit key like ORDER BY (main_flag = 'Y') DESC (MySQL) or ORDER BY CASE WHEN main_flag = 'Y' THEN 0 ELSE 1 END when the intent must be unmistakable.
• Putting WHERE rn = 1 in the same query that computes rn. Window functions run after WHERE, so rn is not yet visible there — you get a "column rn does not exist" error. The rank must be materialized in a subquery or CTE first.
• Reaching for GROUP BY + MAX(main_flag). MAX picks the largest flag string but cannot bring along the matching office_id in standard SQL — you would need a correlated join back, which is more code and the same tie ambiguity. Window ranking carries the whole row along for free.
• Forgetting the tie-break. As above: a partition of all-'N' rows yields a non-deterministic pick. Always add a unique secondary sort key when the primary key can tie.

Takeaways

• Ranking turns "prefer X, else fall back" into one filter. ROW_NUMBER() … WHERE rn = 1 is the canonical "pick one row per group" idiom — and the fallback case (one-element group) is handled by counting, not by a special branch.
• Know what your ORDER BY is actually comparing. Here it is two literal strings, and the answer rides on 'Y' > 'N' lexically. Make load-bearing comparisons explicit when the data's encoding could drift.
• A tie in the ORDER BY of a window function is a silent non-determinism bug. If the sort key is not unique within the partition, add a tiebreaker (here , office_id) so the result is reproducible across engines and runs.
• The window function is evaluated after WHERE/GROUP BY but before the outer SELECT's filter — which forces the subquery/CTE wrapping pattern. The outer ORDER BY is cosmetic, not part of the selection logic.

Based on the LeetCode-style "Main Office Assignment for Each Employee" practice problem. Ranking-then-filter idiom per the SQL standard (ISO/IEC 9075) window-function semantics; collation behavior of string comparison per PostgreSQL and MySQL documentation. Re-authored and deepened for this guide: the original asserted that DESC floats the main office but never explained the 'Y' > 'N' lexical mechanism or why the single-office case needs no branch, mislabeled the rank step's output as "Step 2," and left the non-deterministic all-'N' tie-break unflagged — all corrected here, with the deterministic ORDER BY main_flag DESC, office_id fix and a "why the naive flag filter is wrong" note added.