ROW_NUMBER() function in Snowflake?

Explained: ROW_NUMBER() function in Snowflake?

by

Are you looking to find how to use the ROW_NUMBER() function within the Snowflake cloud data warehouse or maybe you are looking for a solution, how to use the ROW_NUMBER function in the Snowflake? If you are looking for any of these problem solutions then you have landed on the correct page. I will also show you what and how to use ROW_NUMBER() function. I will explain the ROW_NUMBER() function by taking a practical example. So don’t waste time let’s start step by step guide to understanding what is the ROW_NUMBER() expression.

What is ROW_NUMBER() Function?

ROW_NUMBER() function helps to return the unique row number within each row. For example, if I want to know specific data i.e. ‘John’ which is present in the 20th row, then the row_number will be displayed for that particular data is 20.

How can we use the ROW_NUMBER() Function ?

ROW_NUMBER() function is used to return a unique row number for each row within a window partition. The row number starts at 1 and continues up sequentially.

What is the Syntax of ROW_NUMBER() function in Snowflake?

-- Syntax : 

ROW_NUMBER () over ( [partition by expression-1[,..]] [order by expression-2,[…]] [ ASC | DESC ])

ROW_NUMBER() Argument Details :

Here we are having only expressions that will be used depending on the requirement.

  • Expression-1 specifies the columns or expressions to partition by. we can partition by 0, 1, or more expressions. For example, If you are selecting data across multiple states and you want a row number from 1 to N within each state then in that case we can partition by the state.
  • Expression-2 specifies the columns or expressions that will be used to determine the order of the rows. We can order 1 or more by expressions. For example, If we want to list the farmers in order by the production of corn.

Full Example of ROW_NUMBER() function in Snowflake:

-- Create a Table

create or replace TABLE EMP (
ID NUMBER(38,0),                                                                         
NAME VARCHAR(30) COMMENT 'NAME OF THE EMPLOYEE',                                                                         
INFO VARCHAR(20) DEFAULT 'NOT-NULL'  
)

-- Insert data

Insert into demo.public.emp(id,name) values(1,'Ram'),(2,'RAJU'),(3,'Sameer'), (4,'Sai Kumar'), (5,'Naveen Sai'), (6,'Seshi Kumar'),(7,'Rajiv Ram');

-- Select the Table data

select * from demo.public.emp;

+----+-------------+----------+
| ID | NAME        | INFO     |
|----+-------------+----------|
|  1 | Ram         | NOT-NULL |
|  2 | RAJU        | NOT-NULL |
|  3 | Sameer      | NOT-NULL |
|  4 | Sai Kumar   | NOT-NULL |
|  5 | Naveen Sai  | NOT-NULL |
|  6 | Seshi Kumar | NOT-NULL |
|  7 | Rajiv Ram   | NOT-NULL |
+----+-------------+----------+

-- Apply ROW NUMBER to table data

select state, bushels_produced, row_number()
  over (order by bushels_produced desc)
  from corn_production;

+--------+------------------+------------+
|  state | bushels_produced | ROW_NUMBER |
+--------+------------------+------------+
| Kansas |              130 |           1|
| Kansas |              120 |           2|
| Iowa   |              110 |           3|
| Iowa   |              100 |           4|
+--------+------------------+------------+

select
  symbol,
  exchange,
  shares,
  row_number() over (partition by exchange order by shares) as row_number
from trades;

+------+--------+------+----------+
|SYMBOL|EXCHANGE|SHARES|ROW_NUMBER|
+------+--------+------+----------+
|SPY   |C       |   250|         1|
|AAPL  |C       |   250|         2|
|AAPL  |C       |   300|         3|
|SPY   |N       |   100|         1|
|AAPL  |N       |   300|         2|
|SPY   |N       |   500|         3|
|QQQ   |N       |   800|         4|
|QQQ   |N       |  2000|         5|
|YHOO  |N       |  5000|         6|
+------+--------+------+----------+

As we can see from the above example we got the row numbers according to the Exchange data as expected.

When you should use ROW_NUMBER() Function in Snowflake?

There are certain use case scenarios when it is recommended to use the ROW_NUMBER function within the Snowflake cloud data warehouse which are as follows:

  • You want to apply the row number to a table data to get the sequential order.
  • You want to get unique id’s for a table data.

Real World Use Case Scenarios for ROW_NUMBER Function in Snowflake.

  • The persons standing in a queue in sequential order.
  • The number plates for vehicle registration.
  • House numbers will also be in such an order to use row number.

ROW_NUMBER() Snowflake Official Documentation Link

Final Thoughts

In this article, we have learned about ROW_NUMBER() function and uses with the examples explained clearly. I have also covered different scenarios with a practical example that could be possible. I hope the information that was provided is helped in gaining the knowledge.

Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.

Join Telegram Group
Download Free Azure Videos
Connect on LinkedIn

Revanth Kumar