Scenario

Control output tuples and row order deliberately: choose DISTINCT grain, sort with secondary keys, define top-N before LIMIT, and make null and tie placement explicit.

DISTINCT, ORDER BY, LIMIT, and Stable Top-N / debug query

One-sentence task

M10-A05 - Debug - repair an unstable top-N result under tied metrics and duplicate names. Control duplicate output tuples and deterministic sorted/limited results.

Structured output contract

Result grain

one product row among the stable top three with tied metrics resolved

Exact columns

product_id; product_name; price

Source population

Use the prompt setup plus FROM, JOIN, WHERE, and subquery predicates as the source population. Visible rows are only examples.

Grouping

Do not collapse rows unless the contract explicitly asks for aggregation, distinct tuples, or set semantics.

Ordering

order by price descending then product_name then product_id

Validation

select-only; hidden deterministic variants.

Relevant tables

Time and difficulty

Estimated time

9 minutes

Difficulty

3/5

Objective and concepts

Debug the requested SQL output contract for distinct, order by, limit, and stable top-n using source grain, columns, ordering, and edge-case evidence.

SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;

SELECT product_id, product_name, price FROM products ORDER BY price DESC, product_id LIMIT 3;

Assumptions, dialect notes, and common traps

Duplicate policy

Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.

Null policy

Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.

Tie-breakers

Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.

Zero-related entities

Do not invent zero rows unless the contract asks for preserved parents, missing entities, or complete periods.

Numeric tolerance

Use exact semantic comparison unless the activity explicitly declares a numeric tolerance.

• DISTINCT works on tuples: A duplicate value in the first selected column is expected to collapse even when another selected column differs. Repair: Match the DISTINCT grain to every selected output column, not only the first expression.
• Table order is not stable evidence: A query looks right because the visible seed happens to appear in the desired order. Repair: Add ORDER BY for every ordered contract before relying on row positions.
• LIMIT is not top by itself: The first N rows are treated as highest rows without defining a descending sort. Repair: Sort by the requested metric and tie-breakers, then apply LIMIT.
• Ties need a unique final key: Rows tied on a metric or name swap order across hidden variants. Repair: Add deterministic secondary keys and end with a unique identifier.

Opened hints

No hints opened yet.