What's the difference between ROW_NUMBER, RANK and DENSE_RANK?

At the Norte branch, March and July 2025 sold exactly the same amount: 132,700 pesos. If you sort the months from highest to lowest sales, what place does each one get? It depends on the function:

• ROW_NUMBER() counts 1, 2, 3... and never repeats. Two tied rows get different numbers.
• RANK() gives tied rows the same number and then skips: if two tie at 9, the next one is 11.
• DENSE_RANK() gives tied rows the same number and doesn't skip: if two tie at 9, the next one is 10.

All three are written the same way, with OVER (ORDER BY ventas DESC). None takes arguments: what changes is how they count when two rows have the same value in that ORDER BY. DuckDB's documentation calls those tied rows peers.

Try it

Norte's January sold 128,900 pesos and comes right after the tie. Before you run anything, commit to the three numbers you think it gets. Then write the query with all three functions at once.

These are the rows around the tie, exactly as the solution returned them in Maestro's sandbox:

Above the tie, all three columns agree. That's why it's so easy to believe they're interchangeable: most examples have no ties. The difference shows up right at the tie and after it.

Which one should you use? Ask yourself what you want to happen with a tie:

• A unique number per row (to keep exactly one): ROW_NUMBER(), with a tiebreaker in the ORDER BY.
• "Everyone in the top 3 places," ties included: RANK(), which counts how many finished above.
• "The 3 highest values," even if several rows share them: DENSE_RANK().

Here are Norte's twelve months sorted from highest to lowest sales. The two highlighted bars are the tie: same height, same value. For RANK() and DENSE_RANK() they're the same place; for ROW_NUMBER() one has to go before the other.

The ties you'll run into

A tie at the top. If two months tie for first, RANK() gives 1, 1 and then 3: there's no second place. DENSE_RANK() gives 1, 1 and 2. If your report says "the second-best month," decide up front what that means when first place is tied.

A missing value. A row with NULL in the column you sort by also gets a place. In a sandbox run with four rows, one of them NULL, RANK() OVER (ORDER BY x DESC) put it in 4th, last: in DuckDB, NULLs go last in descending order too. If you don't want a row with no data competing, filter it out first with WHERE x IS NOT NULL.

A tie you don't see. ROW_NUMBER() without a tiebreaker doesn't tell you there was a tie: it just picks one. If a ranking goes into a report someone will read, check with RANK() for ties at the cutoff, even if you end up using ROW_NUMBER().

The oldest interview question with ties is "what's the second-highest value?", the second-highest-salary question, now per group. With DENSE_RANK() = 2 you get the second distinct value, even if several rows share first place. For Norte, the run gave November, with 156,600 pesos, as rango_denso 2. Another common version: "the three best-selling products in each category, including ties." There, the word "including" decides between RANK() and ROW_NUMBER(). If someone asks you for a top N with ties and you're not sure which function to pick, tell your Maestro about your case.

When you hand over a top 10, note next to the result what you did with ties. "Top 10, ties included (12 rows)" heads off the question of why there are 12, and "top 10 by sales, ties broken by date" keeps anyone from thinking tenth place is a clean cut.