ROW_NUMBER & window ordering

Number rows without grouping them away

Lesson 15 of 20 · 13 min · Module 5: Explain a more complex report

A ranking report often needs every learner’s original row plus a position. A window function adds a calculation without collapsing rows into groups.

Start this lesson →

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

Add a row-level calculation with OVER
Separate window order from display order

The idea

ROW_NUMBER() OVER (ORDER BY ...) assigns a distinct sequence number. The ORDER BY inside OVER controls numbering; the outer ORDER BY controls the final display order.

SELECT name, xp,
  ROW_NUMBER() OVER (ORDER BY xp DESC, name) AS position
FROM learners;

01Keep the detail rows

There is no GROUP BY here. The result still has one row per learner, and the window adds a value to each row.

02Make numbering deterministic

An extra name rule resolves equal XP values consistently. ROW_NUMBER always assigns distinct numbers even if the primary sort values tie.

03Control the final presentation

The window’s ORDER BY does not replace the SELECT’s final ORDER BY. State both when both behaviors matter.

Your turn

Return each learner’s name, xp, and ROW_NUMBER as position, numbered by XP descending and then name ascending. Display the rows in that same order.

What the checks look for

Preserves all five learners and assigns the correct positions

Try it in the editor →

Take this with you

Window functions can add context while keeping the original detail.

Go a little further

Compare ROW_NUMBER, RANK, and DENSE_RANK after introducing a tie into a temporary test dataset.