SolveWithSQL

SQL Beginner Challenge 30: Top-N with Ties (Handling Equal Totals)

Difficulty: Beginner → Early Intermediate
Estimated time: 15–25 minutes
SQL concepts: SUM(), GROUP BY, ORDER BY, LIMIT, tie-breaking
Goal: Produce stable Top-N results when totals are equal.

The Challenge

The product manager asks:

“Can we list our top 3 categories by total value — and make sure the ranking is consistent even when totals tie?”

In real dashboards, ties happen.
If two categories have the same total, the database may return them in an unpredictable order unless you define a tie-break rule.

Database Table

products

column_nametypedescription
product_idINTEGERUnique product ID
nameTEXTProduct name
categoryTEXTProduct category
priceDECIMALProduct price

Sample Data (includes a tie)

product_idnamecategoryprice
201Monitor ADisplays300.00
202Monitor BDisplays100.00
203Keyboard AAccessories200.00
204Mouse AAccessories200.00
205Desk LampWorkspace250.00
206Laptop StandWorkspace150.00

Totals by category:

  • Displays = 400.00
  • Accessories = 400.00 ✅ tie
  • Workspace = 400.00 ✅ tie

Your Task

Write a SQL query that returns:

  • category
  • total_value (sum of prices)
  • The top 3 categories by total value
  • With a consistent ordering when totals are tied

Expected Output (stable ordering)

categorytotal_value
Accessories400.00
Displays400.00
Workspace400.00

Important: Because all totals are equal, you must apply a tie-break rule (e.g., alphabetical by category).

✅ Constraints

  • Use SUM()
  • Use GROUP BY
  • Use ORDER BY
  • Use LIMIT 3
  • Add a secondary sort to break ties
  • Do not use SELECT *

Hint (Optional)

If you only do:

ORDER BY total_value DESC

…then tied rows can appear in any order.
To stabilize ranking, add another column in ORDER BY.

Example tie-breakers:

  • category ASC (alphabetical)
  • category_id ASC (best if available)

SELECT
category,
SUM(price) AS total_value
FROM products
GROUP BY category
ORDER BY total_value DESC, category ASC
LIMIT 3;

Explanation

  • SUM(price) calculates category totals
  • ORDER BY total_value DESC ranks highest totals first
  • , category ASC ensures ties are always sorted the same way
  • LIMIT 3 returns only the top three rows

This makes dashboards consistent and avoids confusing “random” ordering.

Common Mistakes

  • Using LIMIT without stable ordering
  • Forgetting a tie-break rule
  • Assuming the database will return ties alphabetically (it won’t)

🔑 Key Insight

If rankings can tie, always define a tie-breaker.

This is a professional habit that improves:

  • repeatability
  • trust in reporting
  • user experience

🔗 Next Step

You’ve now completed a strong beginner block covering:

  • Filtering
  • Joins
  • Aggregation
  • Ranking and Top-N reporting

A natural next move is to start an Intermediate track, for example:

  • Subqueries
  • Window functions (ROW_NUMBER, RANK)
  • “Top product per category”
  • Rolling totals and time-series analytics