Add a row number to result set of a SQL query

Picking the right ORDER BY

Let's pick the proper column for the ORDER BY clause. Order by a non-unique/non-indexed column, and your results might show up "fashionably late" to each execution. To keep your row numbering consistently "on time", use a unique column or index:

SELECT ROW_NUMBER() OVER (ORDER BY unique_key_column) AS RowNum, *
FROM your_table;


Channel your inner speedy Gonzalez 🐭 by leveraging indexes. Composite keys of a clustering index, or coverage indexes are not only high performance but ensures consistent row numbering. Talk about efficiency!

Numbering like a pro

If you're feeling adventurous and want complex numbering schemes, let's dive into SQL's versatile window functions:

Partitioned Row Numbers

SELECT ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS Rn, *
FROM products;


This query gives you a row number within each 'category', starting a new count for each category. Pretty neat, huh?

Then, we have RANK() and DENSE_RANK() stepping in to help with ties at the finish line:

SELECT RANK() OVER (ORDER BY score DESC) AS `Rank`, *
FROM game_scores;


RANK() is the chivalrous function that gives the same rank to ties, then tactfully skips the next rank. But if you need consecutive numbering, even with ties, use DENSE_RANK(). It's that rider who never skips a beat.

When ROW_NUMBER() is a wallflower

In scenarios where ROW_NUMBER() isn't quite cutting it, or if you're tackling a unique problem, consider peeking into Paul White's advanced number series solutions. White's detailed musings will guide you on custom numbering patterns, from recursive CTEs to tally tables, mapping uncharted territories of SQL numbering.

Coding with wit and wisdom

Sometimes ROW_NUMBER() alone just won't do. Spicing it up with some system functions can give your quest a twist:

Random row numbers (because, why not?):

SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS Rn, *
FROM your_table;


Predictable row numbers across sessions (old is gold):

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Rn, *
FROM your_table;


Love your data consistency? Don't forget about transaction consistency and database lock level when fetching concurrent row numbers.

Don't ignore the underdogs! WITH (NOLOCK) and similar transaction isolation-level clauses can be your secret sauce to prevent blocking.