Assign Sequential Row Numbers Within Partitions

Snowflake ROW_NUMBER: Assign Sequential Numbers to Rows

The ROW_NUMBER() is a SQL function that assigns a sequential integer to each row within a partition of a result set. It starts numbering from 1 for each partition, making it ideal for tasks like ranking items within categories.

What it does?

ROW_NUMBER() gives each row in a result set a unique number based on the order specified. This is particularly useful when you need to sort or rank data within categories (partitions).


Here’s how you use the ROW_NUMBER() function in Snowflake:

[ PARTITION BY <expr1> [, <expr2> ...] ]
ORDER BY <expr3> [, <expr4> ...] [ASC | DESC]


  • PARTITION BY: (Optional) Divides the result set into partitions where the ROW_NUMBER() resets to 1 for each partition.
  • ORDER BY: Determines the order of rows in each partition; this is where the numbers are assigned.

Important notes

  • No Arguments: The ROW_NUMBER() function itself does not take any parameters inside the parentheses.
  • Partitioning: Partitioning is not mandatory. If omitted, the entire result set is treated as a single group.
  • Ordering: You must specify an order. If you are partitioning, the numbering within each partition is based on this order.

Common Uses of ROW_NUMBER()

  • Ranking Items within Categories: In e-commerce platforms, ROW_NUMBER() can be used to rank products within each category based on criteria like sales volume, customer reviews, or recent sales trends.
  • Top-N Queries: When you need to retrieve the top or bottom N records within each group, such as the top 3 salespersons in each region or the worst performing students in each class, ROW_NUMBER() can be used to easily filter these records.
  • Removing Duplicates: If your data contains duplicates and you want to delete them while keeping one record for each set of duplicates, ROW_NUMBER() can help identify and retain the first occurrence of each duplicated entry based on a specific order.
  • Pagination: In applications where data needs to be displayed in a paginated format, ROW_NUMBER() can assign row numbers and then select only a specific range of those numbers to show a subset of results on each page.
  • Sequential Numbering of Events or Transactions: In logs or transaction records, ROW_NUMBER() can be used to sequentially number events as they occur over time, regardless of their grouping or categorization.


Ranking Data Within Partition

Imagine you have a table that tracks how many times each character from “The Hitchhiker’s Guide to the Galaxy” has visited various planets. Let’s write a SQL query to rank these characters based on the number of visits to each planet.


Table: character_visits

1Arthur DentEarth100
1Arthur DentMagrathea5
2Ford PrefectBetelgeuse Five50
2Ford PrefectEarth75
3Zaphod BeeblebroxBetelgeuse Five85
3Zaphod BeeblebroxMagrathea10

Snowflake SQL Query

SELECT character_name, planet, visits,
) AS travel_rank
FROM character_visits;


Zaphod BeeblebroxBetelgeuse Five851
Ford PrefectBetelgeuse Five502
Arthur DentEarth1001
Ford PrefectEarth752
Zaphod BeeblebroxMagrathea101
Arthur DentMagrathea52


  • Partition by planet: The data is partitioned by the planet, meaning the ROW_NUMBER() function resets for each planet.
  • Order by visits DESC: Within each partition, the data is ordered by the number of visits in descending order. The character with the most visits to a planet will have a travel rank of 1.
Y42 SQL completion user interface

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Start for free