Ranking Window Functions: ROW_NUMBER(), RANK(), and DENSE_RANK()

In a previous post, we introduced window functions to add “group context” without collapsing rows. Now let’s build on that foundation with a set of window functions that answer a very human question:

Where does this row fit among its peers?

That’s exactly what ranking functions do. They assign a position (or rank) to each row based on an ordering you choose.

A Ranking Always Needs an ORDER BY

Ranking functions work inside OVER(), and they almost always include an ORDER BY:

RANKING_FUNCTION()
OVER (
  PARTITION BY some_column
  ORDER BY some_column
)

RANKING_FUNCTION()
OVER (
  PARTITION BY some_column
  ORDER BY some_column
)

Two quick reminders:

• PARTITION BY defines the peer group (optional, but common)
• ORDER BY defines what “best” means (required for ranking to be meaningful)

Meet the Three Ranking Functions

All three functions below can look similar at first glance, but they behave differently when there are ties.

• ROW_NUMBER() → unique sequence number per row (no ties allowed)
• RANK() → ties share the same rank, and the next rank is skipped
• DENSE_RANK() → ties share the same rank, and the next rank is not skipped

If that felt abstract, don’t worry — the examples make it obvious.

Example: Rank Paintings by Year Within Each Artist

Using the Gallery.Paintings table, we’ll rank each artist’s paintings from oldest to newest by YearCreated.

ROW_NUMBER(): Always unique

SELECT Title,
       Artist,
       YearCreated,
       ROW_NUMBER() OVER (
         PARTITION BY Artist
         ORDER BY YearCreated
       ) AS RowNum
FROM Gallery.Paintings;

SELECT Title,
       Artist,
       YearCreated,
       ROW_NUMBER() OVER (
         PARTITION BY Artist
         ORDER BY YearCreated
       ) AS RowNum
FROM Gallery.Paintings;

What it does:

• Every row gets a unique number
• If two paintings share the same year, one will still be “first” and one will be “second” (because SQL has to pick an order)

RANK(): Ties share a rank (and ranks can skip)

SELECT Title,
       Artist,
       YearCreated,
       RANK() OVER (
         PARTITION BY Artist
         ORDER BY YearCreated
       ) AS RankNum
FROM Gallery.Paintings;

SELECT Title,
       Artist,
       YearCreated,
       RANK() OVER (
         PARTITION BY Artist
         ORDER BY YearCreated
       ) AS RankNum
FROM Gallery.Paintings;

What it does:

• If two rows tie, they get the same rank
• The next rank jumps ahead

DENSE_RANK(): Ties share a rank (no skipping)

SELECT Title,
       Artist,
       YearCreated,
       DENSE_RANK() OVER (
         PARTITION BY Artist
         ORDER BY YearCreated
       ) AS DenseRankNum
FROM Gallery.Paintings;

SELECT Title,
       Artist,
       YearCreated,
       DENSE_RANK() OVER (
         PARTITION BY Artist
         ORDER BY YearCreated
       ) AS DenseRankNum
FROM Gallery.Paintings;

What it does:

• If two rows tie, they get the same rank
• The next rank continues normally

A Tie Example You Can Actually See

Your current paintings data doesn’t obviously include ties for YearCreated within the same artist, so here’s a small “pretend result” to illustrate ties clearly.

Imagine an artist with four paintings, and two of them share the same year:

Here’s how each ranking function behaves when ORDER BY YearCreated:

Notice:

• ROW_NUMBER() never repeats
• RANK() repeats 1 for both 1928 paintings, then skips from 1 to 3
• DENSE_RANK() repeats 1 for both 1928 paintings, then continues to 2

That’s the whole difference.

So… Which One Should You Use?

Use ROW_NUMBER() when:

• You need a unique row position
• You’re picking “the top 1 per group”
• You don’t care about ties (or you break them intentionally)

Use RANK() when:

• You want ties to share the same position
• You’re okay with “gaps” in the ranking

Use DENSE_RANK() when:

• You want ties to share the same position
• You do not want gaps

A quick practical example:

• “Top 3” often uses DENSE_RANK() if ties should count together
• “Pick exactly one” often uses ROW_NUMBER()

One More Important Tip: Break Ties on Purpose

If you use ROW_NUMBER() and your ORDER BY column isn’t unique, the database may choose an arbitrary order between ties.

To make results stable and predictable, add a second sort column:

SELECT Title,
       Artist,
       YearCreated,
       ROW_NUMBER() OVER (
         PARTITION BY Artist
         ORDER BY YearCreated, Title
       ) AS RowNum
FROM Gallery.Paintings;

SELECT Title,
       Artist,
       YearCreated,
       ROW_NUMBER() OVER (
         PARTITION BY Artist
         ORDER BY YearCreated, Title
       ) AS RowNum
FROM Gallery.Paintings;

Now ties break consistently using Title.

Key Takeaways

• Ranking functions assign positions to rows within a defined peer group.
• PARTITION BY defines the group; ORDER BY defines the ranking criteria.
• ROW_NUMBER() is always unique per row.
• RANK() allows ties and leaves gaps after ties.
• DENSE_RANK() allows ties and never leaves gaps.
• Add a secondary sort key if you want stable, deterministic results.