Ranking Rows in SQL: ROW_NUMBER, RANK, DENSE_RANK, and NTILE

A window function computes a value across a set of related rows without collapsing them — each row keeps its place in the output, but gains a calculation drawn from its neighbours. The switch that turns an ordinary function into a window function is the OVER clause, and inside it, PARTITION BY chooses the groups while ORDER BY decides the sequence within each group.

Four Functions, Two Different Questions

Four of the most immediately useful window functions build directly on that idea, though they split into two families. The first three assign a position to each row, based on the order you specify. ROW_NUMBER() gives every row a unique, sequential integer — no exceptions, no repeats. RANK() gives tied rows the same number, then jumps ahead to account for them. DENSE_RANK() also gives tied rows the same number, but never jumps — the next rank is always the very next consecutive integer. That single difference — how each function treats a tie, and whether it leaves a gap afterward — is the heart of the first half of this article.

The fourth function asks a completely different question. NTILE(n) doesn't rank rows against each other at all — it divides them into n roughly equal-sized buckets, numbered 1 through n. It's the standard tool behind quartiles, deciles, and similar equal-bucket segmentation. We cover it here because it shares the same partitioning machinery and the same "ranking family" umbrella as the other three, even though the problem it solves — grouping, not ranking — is fundamentally different.

The Sample Dataset

We use the familiar customers and orders tables. To make ties actually visible — the whole point of comparing RANK() against ROW_NUMBER() — two extra orders have been added: order 106 duplicates Alice's 89.50 total, and order 107 duplicates Bob's 60.00 total. This is called out explicitly so the dataset stays traceable. As a bonus, seven order rows is also a convenient number for the NTILE() section later — it doesn't divide evenly into most bucket counts, which is exactly what makes the remainder rule visible.

customers

orders (with two extra rows to create ties)

ROW_NUMBER() — A Unique Number for Every Row

ROW_NUMBER() does exactly what its name promises: it counts off the rows within each partition, in the order you specify, giving each one a distinct integer starting at 1. Within any single partition, no two rows ever receive the same number — even if their values are identical.

ORDER BY is essential whenever you want the row number to represent a meaningful ordering. Without it, the database is free to number the rows in an unspecified order — you should never rely on physical storage order, execution plans, or the order in which rows happen to be returned. Some engines (like SQL Server) require ORDER BY outright; others (like PostgreSQL, SQLite, and Redshift) permit omitting it, but the resulting numbering carries no useful ranking meaning. ROW_NUMBER() only becomes useful once you tell it what order defines "first."

Let's number each customer's orders from most to least expensive:

Look closely at customer 1's two tied orders (101 and 106, both 89.50). ROW_NUMBER() still assigns them different numbers — 1 and 2 — because it never allows duplicates. But which of the two tied rows gets 1 and which gets 2 is, strictly speaking, non-deterministic: the database is free to pick either order. To make the outcome predictable, break the tie yourself by adding a second column to the ORDER BY — typically a unique identifier like the primary key:

Now order 101 is guaranteed to receive row_num = 1 and order 106 to receive row_num = 2, because whenever two rows tie on total, the lower order_id wins the position by rule. If your application logic depends on a specific row winning a tie — pagination cursors, "the first" of anything, deduplication with a preferred survivor — always add a deterministic tiebreaker column.

RANK() — Ties Share a Position, Then Skip Ahead

RANK() uses the same syntax as ROW_NUMBER(), but handles ties completely differently: rows with equal values in the ORDER BY receive the same rank. The rank that follows a tie is not the next consecutive integer — it jumps ahead by however many rows tied, leaving a gap.

Apply it to the same partition-ordered data:

Both 89.50 orders tie for rank 1. The very next row does not get rank 2 — it gets rank 3, because two rows already occupy the ranks "used up" by the tie. This is the defining, unmistakable signature of RANK(): ties share a position, and a gap opens immediately afterward.

DENSE_RANK() — Ties Share a Position, No Gap Follows

DENSE_RANK() starts from the exact same idea as RANK() — rows with equal values in the ORDER BY receive the same rank — but resolves the aftermath differently. Once a tie is resolved, DENSE_RANK() simply continues counting from the next whole number. No rank is ever skipped, no matter how many rows tied for the position before it.

Apply it to the very same tied rows:

Both 89.50 orders still tie for rank 1, exactly as with RANK(). But the next row now gets rank 2, not 3. DENSE_RANK() counts distinct values, not rows — the number always tells you "how many different values have appeared so far," which is precisely why it never leaves a hole.

ROW_NUMBER(), RANK(), and DENSE_RANK() — Side by Side

The contrast is clearest when all three functions run in the same query, against the same ordering, so you can watch them diverge on the exact same rows:

On rows without a tie, all three columns agree perfectly. The moment a tie appears — orders 101 and 106 — the three functions tell three different stories: row_num keeps counting 1, 2, 3 as if nothing happened; rnk freezes at 1, 1 and then leaps to 3, skipping the number 2 entirely; dense_rnk also freezes at 1, 1, but then continues smoothly to 2, with no number ever missing.

NTILE(n) — Equal-Sized Buckets, Not Ranks

NTILE(n) steps outside the ranking logic entirely. Instead of comparing rows to decide who comes "first," it takes the ordered rows in a partition and distributes them into n buckets, as evenly as possible, numbering the buckets 1 through n. It answers a different question: not "where does this row stand?" but "which slice does this row fall into if I split everything into n equal groups?"

What happens when the row count doesn't divide evenly by n? The rule is precise and consistent across engines: the earlier buckets absorb the extra rows, one each, until the remainder is used up. Every bucket's size differs from any other by at most one row — never more.

Let's split all seven orders into three buckets by total value, largest first:

7 ÷ 3 = 2 remainder 1, so bucket 1 gets the extra row: three rows, while buckets 2 and 3 get two each (3 + 2 + 2 = 7). Notice that orders 105 and 107 tie at 60.00, yet NTILE() made no special effort to keep them together — they simply both landed in bucket 2 because of where they fell in the row order. NTILE() distributes by position, not by value equality; had the boundary fallen between them, they would have been split across two buckets without complaint. That's a meaningful contrast with RANK() and DENSE_RANK(), which assign the same rank to rows that are equal on the ORDER BY expressions — deliberately keeping tied values together.

Practical Use Cases

Deduplication — keep only one row per group

A very common cleanup task: keep just one order per customer and discard the rest. Here we take the row with the highest order_id — a reasonable stand-in for "most recent" when IDs are assigned in insertion order, though in a real schema you'd typically order by an explicit created_at timestamp. Wrap ROW_NUMBER() in a CTE, then filter on row_num = 1 in the outer query.

Because ROW_NUMBER() never produces duplicate values within a partition, this pattern is guaranteed to return exactly one row for each distinct customer_id present in orders — the deduplication is unambiguous by construction. Note that customers with no orders at all won't appear here; if you need one row for every customer in the customers table, you'd join this result back to customers.

Pagination

ROW_NUMBER() can implement page windows explicitly — for example, rows 11 through 20 for "page 2" of a result set:

In many engines, LIMIT/OFFSET or OFFSET ... FETCH accomplish simple pagination more concisely. The ROW_NUMBER() approach earns its keep when you need finer control — for instance, computing the row number once and reusing it for several different page slices, or combining pagination with other window calculations in the same query.

Top-N per group

A frequent analytical request: "show me the top order for each customer." This is where the choice between the two functions actually changes your results, not just your syntax:

For customer 1, both order 101 and order 106 sit at rank 1 (they are tied at 89.50), so RANK() = 1 returns both rows. Swap in ROW_NUMBER() instead and you would get exactly one row per customer, arbitrarily choosing between the tied orders unless you add a tiebreaker. Choose RANK() when ties should legitimately produce more than one "winner"; choose ROW_NUMBER() when you need precisely one row, no matter what.

There is a third option worth knowing: if what you actually want is the "top N distinct spend levels" rather than the top N rows, DENSE_RANK() is the right tool. WHERE dense_rnk <= 2 reliably returns every row belonging to the two highest distinct totals per customer — regardless of how many rows share each of those totals. RANK() <= 2 cannot make that same promise: because it skips numbers after a tie, a value that ties for first can push the second-lowest distinct value out of range entirely.

Leaderboards

Competitive rankings are the natural home of RANK(). Think of Olympic standings: two athletes can share the silver medal, and the next athlete is placed fourth — not third. That is exactly the gap behaviour RANK() produces automatically. If instead you want the next athlete placed third — because your scoreboard should never show an unused position — that is exactly what DENSE_RANK() delivers.

Quartile segmentation

Where the first three functions answer questions about individual rows, NTILE() shines at grouping an entire population. Splitting customers into spending quartiles for a marketing campaign is the textbook case:

Quartile 1 becomes your top spenders, quartile 4 your lowest — a ready-made segment you can label "VIP" or "At risk" without a single manual cutoff. At production scale, with thousands of customers, each quartile becomes a genuinely useful cohort rather than the single-customer buckets a four-row example produces.

Common Pitfalls

• Forgetting ORDER BY inside OVER. Without it, both functions still run — but the "position" they assign has no defined meaning, since there is no rule for which row comes first. Always pair a ranking window function with an explicit ORDER BY.
• Assuming ROW_NUMBER() is stable across ties. When two rows are equal on your ORDER BY columns, which one gets the lower number is undefined behaviour unless you add a deterministic tiebreaker (a unique column, typically a primary key) to the ORDER BY list.
• Trying to filter on a window function directly in WHERE. This will raise an error:
  SELECT order_id, ROW_NUMBER() OVER (ORDER BY total) AS rn FROM orders WHERE rn = 1; -- ERROR: rn does not exist yet at this stage
  This fails because of SQL's logical processing order: FROM → WHERE → GROUP BY → HAVING → window functions → SELECT → ORDER BY. Window functions are computed after WHERE has already run, so WHERE cannot see their results. The fix is always the same: compute the window function inside a CTE or subquery, then filter on it in the outer query — exactly the pattern used in every example above.
• Assuming RANK() and DENSE_RANK() are interchangeable. They agree completely when there are no ties — the moment a tie appears, they diverge. Picking the wrong one silently changes which rows count as "top N": RANK() <= N can return fewer distinct values than you expect (or exclude rows you wanted) whenever a tie consumes extra rank numbers. If your intent is "the top N distinct values," reach for DENSE_RANK(); if your intent is "positions 1 through N with ties handled competition-style," RANK() is correct — but know which one you mean.
• Expecting NTILE() to respect ties. Unlike RANK() and DENSE_RANK(), NTILE() is purely positional — it has no concept of "these values are equal, keep them together." Two identical values can land in different buckets if the bucket boundary happens to fall between them in the row order.

Cross-Database Support

ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE() are all widely supported standard SQL window functions. PostgreSQL, MySQL 8.0 and later, Microsoft SQL Server (since SQL Server 2005), SQLite 3.25 and later, Oracle, and Amazon Redshift all support them. The core semantics are consistent across engines, though small differences exist — most notably around whether ORDER BY can be omitted (SQL Server requires it; PostgreSQL, SQLite, and Redshift permit it) and other engine-specific details. For any real ranking or bucketing use case, however, you'll always want ORDER BY, and the syntax and behaviour will match across engines.

Which One Should You Reach For?

The first three come down to one question: should tied rows share a position — and if so, should a gap follow? NTILE() answers a separate question entirely: how do I split this data into equal-sized groups?

• Reach for ROW_NUMBER() when you need a strictly unique, sequential number — deduplication, pagination, or "give me exactly one row per group."
• Reach for RANK() when ties are meaningful and should be reflected as shared positions with a gap afterward — competition-style rankings, leaderboards, or any "top-N" query where a genuine tie should return more than one winner, and the numbering should reflect how many rows a tie "used up."
• Reach for DENSE_RANK() when ties should share a position without leaving a hole in the sequence — most often when you want the "top N distinct values" rather than the top N rows, or when a gapless scale simply reads better to your users.
• Reach for NTILE(n) when the goal isn't ranking at all, but dividing an ordered set into n roughly equal-sized groups — quartiles and percentiles for reporting, spending or engagement tiers for segmentation, or splitting a table into batches for downstream processing.

All four use the same OVER partitioning and ordering machinery; what changes is whether they care about ties at all, and whether they're answering "where do I stand?" or "which group am I in?" Once those distinctions are second nature, choosing between them becomes automatic.