0Pricing
SQL Academy · Lesson

ROW_NUMBER, RANK, DENSE_RANK

Number and rank rows within partitions, and pick the right ranking function for ties.

ROW_NUMBER, RANK, DENSE_RANK is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Three Numbering Functions

Three window functions that number rows within a partition:

  • ROW_NUMBER() — 1, 2, 3, 4 (always unique)
  • RANK() — 1, 2, 2, 4 (ties share, then skip)
  • DENSE_RANK() — 1, 2, 2, 3 (ties share, no skip)

ROW_NUMBER

Assigns 1, 2, 3 within each partition. Ties are broken arbitrarily — provide a tie-breaker in ORDER BY for determinism:

SELECT id, user_id, total,
       ROW_NUMBER() OVER (
         PARTITION BY user_id ORDER BY total DESC, id
       ) AS rn
FROM orders;

Top-N Per Group

The classic ROW_NUMBER use:

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY user_id ORDER BY total DESC
         ) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn <= 3;
-- Each user's top 3 orders.

RANK

Rows with the same ORDER BY value get the same rank; the next distinct value skips over the gap:

-- totals: 100, 90, 90, 70
-- RANK:    1,   2,  2,  4    (skipped 3)
SELECT id, total,
       RANK() OVER (ORDER BY total DESC) AS rk
FROM orders;

DENSE_RANK

Same as RANK but without gaps:

-- totals: 100, 90, 90, 70
-- DENSE:   1,   2,  2,  3
SELECT id, total,
       DENSE_RANK() OVER (ORDER BY total DESC) AS dr
FROM orders;

Picking Between Them

  • Need unique numbers (e.g. pagination)? ROW_NUMBER
  • Ranking with traditional "ties skip"? RANK
  • Ranking with "ties share, next is next"? DENSE_RANK

NTILE: N-Quantile Bucket

NTILE(n) distributes rows into n roughly-equal buckets:

SELECT id, total,
       NTILE(4) OVER (ORDER BY total) AS quartile
FROM orders;
-- 1 = bottom 25%, 4 = top 25%

Removing Duplicate Rows

Use ROW_NUMBER + DELETE to keep one row per duplicate group:

WITH dups AS (
  SELECT id,
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
  FROM users
)
DELETE FROM users WHERE id IN (SELECT id FROM dups WHERE rn > 1);

Pagination Per Group

Page 2 of "each user's orders, 5 per page":

WITH r AS (
  SELECT id, user_id,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
)
SELECT * FROM r WHERE rn BETWEEN 6 AND 10;

PERCENT_RANK and CUME_DIST

Relative position in the partition:

SELECT id, total,
       PERCENT_RANK() OVER (ORDER BY total) AS pct_rank,
       CUME_DIST()    OVER (ORDER BY total) AS cume_dist
FROM orders;

Performance

Numbering functions need sorted input within each partition. An index on (partition_col, order_col) can avoid the sort step.

Recap

Three numbering functions cover most needs.

  • ROW_NUMBER for uniqueness
  • RANK / DENSE_RANK for ranking with ties
  • NTILE for quantile buckets

Quick Check

Given totals 100, 90, 90, 70, which output does DENSE_RANK produce?

Frequently asked questions

Is the “ROW_NUMBER, RANK, DENSE_RANK” lesson free?

Yes — the full text of “ROW_NUMBER, RANK, DENSE_RANK” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “ROW_NUMBER, RANK, DENSE_RANK”?

Number and rank rows within partitions, and pick the right ranking function for ties. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “ROW_NUMBER, RANK, DENSE_RANK” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. OVER, PARTITION BY, ORDER BY
  2. ROW_NUMBER, RANK, DENSE_RANK
  3. LAG, LEAD and Time-Series Patterns
  4. Running Totals and Moving Averages
← Back to SQL Academy