Snowflake
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).

Syntax

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


_10
ROW_NUMBER() OVER (
_10
[ PARTITION BY <expr1> [, <expr2> ...] ]
_10
ORDER BY <expr3> [, <expr4> ...] [ASC | DESC]
_10
)

where:

  • 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.

Examples

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.

Input

Table: character_visits

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

Snowflake SQL Query


_10
SELECT character_name, planet, visits,
_10
ROW_NUMBER() OVER (
_10
PARTITION BY planet
_10
ORDER BY visits DESC
_10
) AS travel_rank
_10
FROM character_visits;

Output

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

Explanation

  • 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