`ROW_NUMBER`, `RANK` and `DENSE_RANK`: number and sort rows

Sorting a result does not assign a position that we can reuse. If we need to choose each customer’s most recent order, number results, or represent ties, we use sorting functions.

All three functions work with OVER, the clause that defines the rows and order used by the calculation. The difference appears when several rows share the same logical position.

The idea

`ROW_NUMBER`: assign a different number to each row

ROW_NUMBER() OVER
(
    PARTITION BY ColumnaDeGrupo
    ORDER BY ColumnaDeOrden
)

`RANK`: preserve ties and leave gaps

RANK() OVER
(
    PARTITION BY ColumnaDeGrupo
    ORDER BY ColumnaDeOrden
)

`DENSE_RANK`: preserve ties without leaving gaps

DENSE_RANK() OVER
(
    PARTITION BY ColumnaDeGrupo
    ORDER BY ColumnaDeOrden
)

Example: most recent order for each customer

;WITH PedidosNumerados AS
(
    SELECT
        p.IdPedido,
        p.IdCliente,
        p.FechaPedido,
        p.Estado,
        ROW_NUMBER() OVER
        (
            PARTITION BY p.IdCliente
            ORDER BY p.FechaPedido DESC, p.IdPedido DESC
        ) AS Numero
    FROM dbo.Pedido AS p
)
SELECT
    IdPedido,
    IdCliente,
    FechaPedido,
    Estado
FROM PedidosNumerados
WHERE Numero = 1;

Each client starts their own numbering. Descending order places the most recent order first and IdPedido resolves a possible date tie.

Compare the treatment of ties

SELECT
    p.Nombre,
    p.Precio,
    ROW_NUMBER() OVER (ORDER BY p.Precio DESC) AS Numero,
    RANK()       OVER (ORDER BY p.Precio DESC) AS Posicion,
    DENSE_RANK() OVER (ORDER BY p.Precio DESC) AS PosicionDensa
FROM dbo.Producto AS p;

If the ordered prices were 800, 800 and 600, we would obtain:

Price	`ROW_NUMBER`	`RANK`	`DENSE_RANK`
800	1	1	1
800	2	1	1
600	3	3	2

ROW_NUMBER physically separates tied rows. RANK skips position 2. DENSE_RANK continues without gaps.

Choose a function according to the need

To select exactly one row per customer we need ROW_NUMBER with a complete tiebreaker. For a leaderboard where two products can share a spot, RANK or DENSE_RANK usually better represents the intent.

What should be checked

If the ORDER BY of ROW_NUMBER does not distinguish every row, the assignment among ties may change from one execution to another. Adding a stable key—a unique value that does not change between executions—at the end of the order makes the choice deterministic: the same set produces the same selected row.

The ORDER BY inside OVER controls the sorting, but does not guarantee the visible order of the result. The outer query needs its own ORDER BY when the presentation must preserve a sequence.

ROW_NUMBER, RANK and DENSE_RANK: number and sort rows

The idea

ROW_NUMBER: assign a different number to each row

RANK: preserve ties and leave gaps

DENSE_RANK: preserve ties without leaving gaps