ROW_NUMBER and top-N per group

sqlrelational-datasqlitepostgresqlindependent-practice

Saved on this device

Full lesson · 4 learning steps Open any exercise for focused practice

Step 1 of 4 · intro card

ROW_NUMBER and top-N per group

Concept and contract

Rank an eligible population with a deterministic tie-breaker, then filter the ranked result.

ROW_NUMBER assigns a sequence within each partition without collapsing its source rows. The worked query chooses the highest known-salary active role per company. Eligible rows are filtered before ranking, and each company receives its own numbering sequence.

The expected winners are A role 1, B role 4 and C role 6. Company D has no eligible row and is not part of this particular output. Retaining every company is a different final join requirement reserved for the capstone.

Mechanism and worked example

The window partitions by company_id and orders salary descending, then id ascending to resolve equal values deterministically.

The window partitions by company_id and orders salary descending, then id ascending to resolve equal values deterministically. A surrounding query filters rn=1 because window results are not available to the same query's WHERE clause under the portable syntax used here.

The outer ORDER BY controls presentation of winners; the window ORDER BY controls numbering inside each company. These are different responsibilities. An outer sort cannot repair a ranking computed with the wrong internal order or partition.

Complete worked example

sql

WITH ranked AS (SELECT id, company_id, ROW_NUMBER() OVER
 (PARTITION BY company_id ORDER BY salary DESC,id) AS rn
 FROM roles WHERE active=1 AND salary IS NOT NULL)
SELECT company_id,id FROM ranked WHERE rn=1 ORDER BY company_id;

Reasoning: The sequence restarts per company and selects the highest known salary.

Counterexamples and common mistakes

The counterexample orders salary ascending and therefore chooses the lowest known-salary record.

The counterexample orders salary ascending and therefore chooses the lowest known-salary record. It is deterministic but answers the opposite question. Omitting the identity tie-breaker would introduce a separate ambiguity when equal salaries compete for a single selected position.

Decide whether the business request wants exactly N rows or all rows tied at the cutoff. ROW_NUMBER can enforce a row-count cap, while ranking functions with peer ties express different policies. Do not treat those functions as interchangeable spelling variants.

Counterexample: a plausible but wrong query

sql

WITH ranked AS (SELECT id, company_id, ROW_NUMBER() OVER
 (PARTITION BY company_id ORDER BY salary ASC,id) AS rn
 FROM roles WHERE active=1 AND salary IS NOT NULL)
SELECT company_id,id FROM ranked WHERE rn=1 ORDER BY company_id;

Reasoning: Ascending salary chooses a different winner.

Independent practice: small query

Return up to two highest known-salary active roles per company, including their assigned positions.

Return up to two highest known-salary active roles per company, including their assigned positions. Keep the same eligibility and ordering rules and change only the rank threshold. Predict A roles 1 and 2, B roles 4 and 8, and C role 6.

The smaller task reuses the worked ranked stage. Verify that a company with only one eligible role still contributes one row and that known zero role 8 remains eligible. Missing role 3 does not enter this known-observation ranking.

Step 1: Keep the ranked eligible stage. Step 2: Return positions one and two for each company.

Practice starter: solve before reading the solution

sql

-- Reuse the worked ranked stage.
-- Return company_id,id,rn for positions at most two.

Reasoning: Change the post-ranking threshold, not the source population.

Hint 1: Use rn<=2 in the outer query.
Hint 2: Order by company_id and rn for the acceptance table.

Solution, tests and trade-offs

The solution filters rn<=2 and preserves company and position in the output.

The solution filters rn<=2 and preserves company and position in the output. It produces five rows, with positions restarting at one for each company. A global LIMIT 2 would return only two rows across all companies and violate the per-group requirement.

Acceptance compares exact identity-position pairs rather than only checking that positions never exceed two. A ranking with the wrong salary direction can satisfy that numeric bound while choosing entirely wrong roles. Both eligibility and order need evidence.

Practice solution and acceptance checks

sql

WITH ranked AS (SELECT id, company_id, ROW_NUMBER() OVER
 (PARTITION BY company_id ORDER BY salary DESC,id) AS rn
 FROM roles WHERE active=1 AND salary IS NOT NULL)
SELECT company_id,id,rn FROM ranked WHERE rn<=2 ORDER BY company_id,rn;

Reasoning: The five identity-position pairs preserve zero and exclude unknown salaries.

Interview reasoning and next step

In an interview, explain partition, window ordering, tie policy and post-window filtering.

In an interview, explain partition, window ordering, tie policy and post-window filtering. Walk one company's eligible rows before showing the complete statement. This reveals whether you understand the grouping boundary rather than merely remembering a top-N template.

The next lesson preserves peer ties with RANK and DENSE_RANK. Its display ordering deliberately includes an identity key outside the rank expression so equal measured values can remain peers while the displayed result is still deterministic.

Optional stretch — after the core task works: Add equal salaries within one company and compare exactly-two with include-cutoff-ties requirements.

Computer practice: https://applydjinn.com/typorithm/sql-lab-20261105.zip. Load the shared setup.sql fixture before running a snippet. The local verifier compares exact ordered row arrays in SQLite and PostgreSQL, keeps SQL values separate from driver bindings and never connects to the production website database.

Rank an eligible population with a deterministic tie-breaker, then filter the ranked result.