SQL Server Interview Question #93

What is ROW_NUMBER()?

Aggregate Functions, GROUP BY & Window Functions Mid-Level Intermediate

Detailed Explanation

ROW_NUMBER assigns a unique sequential integer to rows within each window partition according to the specified ORDER BY.

It is useful for ranking, pagination, selecting one row per group, and identifying duplicates. If the ORDER BY values are not unique, the ordering among ties is not guaranteed to be deterministic; add a unique tiebreaker when deterministic results are required.

Code Example

SELECT Id,
       Name,
       Price,
       ROW_NUMBER() OVER
       (
           ORDER BY Price DESC, Id ASC
       ) AS RowNo
FROM dbo.Products;