Snowflake SQL Tutorial 49 - ROW_NUMBER in Snowflake

Snowflake SQL · Episode 49

In this post we are going to cover ROW_NUMBER in Snowflake.

ROW_NUMBER is the first window function in this course that is not an aggregate. It adds nothing up. It numbers the rows: 1, 2, 3 and so on, in an order we choose. No gaps, and no two rows ever get the same number.

We will number the rows, find out why the function refuses to run without an ORDER BY, restart the numbering in each group, see what it does on a tie, and then use it for the thing it is really for: keeping one row per customer.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 49 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

1. Let's create some sample data
2. Let's start by numbering the rows
3. Can we leave the ORDER BY out?
4. Now let's add a partition
5. What happens when two rows are equal?
6. Can we filter on it in WHERE?
7. Is this not just LIMIT and OFFSET?
8. Now let's keep one row per group
9. Three steps, every time

Let's create some sample data

Everything on this page runs against EMPLOYEES and ORDERS, the tables this series has been using throughout. The script below creates EMPLOYEES, so paste it into a worksheet and run it once.

Sample data

-- EMPLOYEES comes from Episode 1 and ORDERS from Level 3. Every episode in
-- this series reuses the same tables, so you only set them up once.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE EMPLOYEES (
    EMP_ID          INT,
    FIRST_NAME      VARCHAR,
    LAST_NAME       VARCHAR,
    EMAIL           VARCHAR,
    DEPT_ID         INT,
    MANAGER_ID      INT,
    SALARY          NUMBER(10,2),
    COMMISSION_PCT  NUMBER(4,2),
    HIRE_DATE       DATE
);

-- Marcus and Priya have the same SALARY. Grace has no DEPT_ID. Both of those
-- facts matter later on this page.
INSERT INTO EMPLOYEES VALUES
    (1, 'Aisha',  'Khan',      'aisha.khan@example.com',      10,    6, 118000.00, NULL,  '2019-03-11'),
    (2, 'Marcus', 'Reid',      'marcus.reid@example.com',     10,    6,  96500.00, NULL,  '2021-07-01'),
    (3, 'Lena',   'Fischer',   'lena.fischer@example.com',    20,    6, 104000.00, 0.05,  '2018-01-22'),
    (4, 'Tomas',  'Novak',     'tomas.novak@example.com',     20,    3,  87250.00, NULL,  '2022-09-05'),
    (5, 'Priya',  'Raman',     'priya.raman@example.com',     30,    6,  96500.00, 0.10,  '2020-11-17'),
    (6, 'Daniel', 'O''Connor', 'daniel.oconnor@example.com',  10, NULL, 131000.00, NULL,  '2016-05-30'),
    (7, 'Sofia',  'Marino',    'sofia.marino@example.com',    30,    5,  71400.00, 0.08,  '2023-02-13'),
    (8, 'Grace',  'Lin',       'grace.lin@example.com',     NULL,    6,  92000.00, NULL,  '2021-04-19');

Two things in that data matter later on. Marcus and Priya have the same salary, and Grace has no department.

Let's start by numbering the rows

Every window function so far in this level has been an aggregate wearing an OVER clause: SUM, COUNT, AVG. ROW_NUMBER is the first one that is not. It takes no arguments at all, so the brackets after the name stay empty, and it does not combine anything.

Let's number everybody by salary, highest first.

Query

SELECT ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS RN,
       FIRST_NAME, DEPT_ID, SALARY
FROM EMPLOYEES
ORDER BY RN;

Result8 rows · 4 columns

RN	FIRST_NAME	DEPT_ID	SALARY
1	Daniel	10	131000.00
2	Aisha	10	118000.00
3	Lena	20	104000.00
4	Marcus	10	96500.00
5	Priya	30	96500.00
6	Grace	NULL	92000.00
7	Tomas	20	87250.00
8	Sofia	30	71400.00

Daniel is 1 on 131,000.00 and Sofia is 8 on 71,400.00. Nothing collapsed, nothing was added up, and no row disappeared. Every row simply gained a number.

Two things about that column are worth naming now, because the rest of this post depends on them. The numbers are consecutive with no gaps, and no two rows share one.

Can we leave the ORDER BY out?

We cannot, and this is unusual among window functions. ROW_NUMBER will not accept an empty OVER (). Let's take the ORDER BY out and see what happens.

Query — this one fails

SELECT ROW_NUMBER() OVER () AS RN, FIRST_NAME FROM EMPLOYEES;

Query failed — Snowflake returned an error

002061 (42601): SQL compilation error:
Window function type [ROW_NUMBER] requires ORDER BY in window specification.

Which is the right call. “Number these rows” without saying in what order is not a question with an answer, and Snowflake declines to invent one rather than handing us a column that would be different on the next run.

Why only this function insists

Compare this with Episode 48. SUM(SALARY) OVER () is perfectly legal, because a total does not depend on order. ROW_NUMBER is entirely about order, so for it the clause is not optional.

Now let's add a partition

ROW_NUMBER takes a PARTITION BY like everything else in this level, and the numbering starts again from 1 in each group.

Query

SELECT DEPT_ID, FIRST_NAME, SALARY,
       ROW_NUMBER() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS RN_IN_DEPT
FROM EMPLOYEES
ORDER BY DEPT_ID NULLS LAST, RN_IN_DEPT;

Result8 rows · 4 columns

DEPT_ID	FIRST_NAME	SALARY	RN_IN_DEPT
10	Daniel	131000.00	1
10	Aisha	118000.00	2
10	Marcus	96500.00	3
20	Lena	104000.00	1
20	Tomas	87250.00	2
30	Priya	96500.00	1
30	Sofia	71400.00	2
NULL	Grace	92000.00	1

Department 10 runs 1, 2, 3. Then 20 starts again at 1, and so does 30. Grace, whose DEPT_ID is NULL, is 1 in a partition of her own. NULL is a partition key like any other value here.

Because the numbering has no gaps and no duplicates, the last number in each partition has to equal the number of people in that partition. Let's check that.

Query

SELECT DEPT_ID, COUNT(*) AS HEADCOUNT
FROM EMPLOYEES GROUP BY DEPT_ID ORDER BY DEPT_ID NULLS LAST;

Result4 rows · 2 columns

DEPT_ID	HEADCOUNT
10	3
20	2
30	2
NULL	1

Four rows. One for each department, and one for Grace, who has none. Each HEADCOUNT is exactly the largest RN_IN_DEPT in that partition.

What happens when two rows are equal?

This is the part to be careful about, and it is the reason Episode 50 exists. First, let's confirm there is a tie at all rather than taking it on trust.

Query

SELECT SALARY, COUNT(*) AS PEOPLE, LISTAGG(FIRST_NAME, ', ') WITHIN GROUP (ORDER BY FIRST_NAME) AS WHO
FROM EMPLOYEES GROUP BY SALARY HAVING COUNT(*) > 1;

Result1 row · 3 columns

SALARY	PEOPLE	WHO
96500.00	2	Marcus, Priya

One row, so there is exactly one shared salary in the table: Marcus and Priya. Now let's number everyone by salary, descending.

Query

SELECT ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS RN,
       FIRST_NAME, SALARY
FROM EMPLOYEES
ORDER BY RN;

Result8 rows · 3 columns

RN	FIRST_NAME	SALARY
1	Daniel	131000.00
2	Aisha	118000.00
3	Lena	104000.00
4	Marcus	96500.00
5	Priya	96500.00
6	Grace	92000.00
7	Tomas	87250.00
8	Sofia	71400.00

In this capture Marcus got 4 and Priya got 5. The important thing is not which of them got which number. It is that nothing in that query decided it.

Read the window’s ORDER BY again. It names SALARY and nothing else, and on SALARY those two rows are identical. So the query expressed no preference between them. And ROW_NUMBER is not allowed to give them the same number, so it had to break the tie somehow.

What a single capture can and cannot tell you

This page is not claiming Snowflake will always put Marcus on 4, and it is not claiming the two would swap on a re-run either. One capture supports neither statement. What the grid does settle is that the choice was not ours, which is reason enough not to build anything on it.

So let's make it our decision instead

The fix is to add a second column to the window’s ORDER BY, one that cannot itself tie. A primary key is the obvious candidate.

Query

SELECT ROW_NUMBER() OVER (ORDER BY SALARY DESC, EMP_ID) AS RN,
       FIRST_NAME, SALARY, EMP_ID
FROM EMPLOYEES
ORDER BY RN;

Result8 rows · 4 columns

RN	FIRST_NAME	SALARY	EMP_ID
1	Daniel	131000.00	6
2	Aisha	118000.00	1
3	Lena	104000.00	3
4	Marcus	96500.00	2
5	Priya	96500.00	5
6	Grace	92000.00	8
7	Tomas	87250.00	4
8	Sofia	71400.00	7

The numbering came out the same as before, but for a different reason. Marcus is 4 because his EMP_ID is 2 and Priya’s is 5. Same numbers, and now they are the numbers we asked for.

The habit worth forming

A deterministic tiebreak is what makes a ROW_NUMBER query repeatable. If your output feeds a deduplication, an export or anything someone will compare against yesterday’s run, put enough columns in the ORDER BY to make every row distinguishable.

Can we filter on it in WHERE?

What ROW_NUMBER is mostly for is filtering. The top three earners, the latest row per customer. So the obvious next query is to keep the rows whose number is at most three. Let's try it.

Query — this one fails

SELECT ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS RN, FIRST_NAME
FROM EMPLOYEES
WHERE RN <= 3;

Query failed — Snowflake returned an error

002063 (42601): SQL compilation error:
Window function [ROW_NUMBER() OVER (ORDER BY EMPLOYEES.SALARY DESC NULLS FIRST)] appears outside of SELECT, QUALIFY, and ORDER BY clauses.

This is the same clause-ordering rule Episode 46 ran into. WHERE is evaluated before window functions are, so at the moment WHERE runs there is no RN in existence for it to test. The alias is not late. It does not exist yet.

A detail in the error worth reading

Look closely at the error text. Snowflake echoes the window specification back as ORDER BY EMPLOYEES.SALARY DESC NULLS FIRST. The query never wrote NULLS FIRST. That is the default being filled in and shown to us. Error messages that quote your own clause back are often quoting the resolved version of it.

So let's wrap it and filter the wrapper

The function goes in an inner query and the WHERE goes on the outside. By the time the outer query runs, RN is an ordinary column.

Query

SELECT RN, FIRST_NAME, SALARY
FROM (SELECT ROW_NUMBER() OVER (ORDER BY SALARY DESC, EMP_ID) AS RN,
             FIRST_NAME, SALARY
      FROM EMPLOYEES) R
WHERE RN <= 3
ORDER BY RN;

Result3 rows · 3 columns

RN	FIRST_NAME	SALARY
1	Daniel	131000.00
2	Aisha	118000.00
3	Lena	104000.00

Three rows: Daniel, Aisha, Lena. The inner query carries the EMP_ID tiebreak we added, so this is a result the query actually asked for.

And because it is only a number, any slice of it works, not just the top of the list. Let's take a page out of the middle.

Query

SELECT RN, FIRST_NAME, SALARY
FROM (SELECT ROW_NUMBER() OVER (ORDER BY SALARY DESC, EMP_ID) AS RN,
             FIRST_NAME, SALARY
      FROM EMPLOYEES) R
WHERE RN BETWEEN 4 AND 6
ORDER BY RN;

Result3 rows · 3 columns

RN	FIRST_NAME	SALARY
4	Marcus	96500.00
5	Priya	96500.00
6	Grace	92000.00

Marcus, Priya and Grace, the rows numbered four to six. BETWEEN on a window function’s output is pagination without an OFFSET.

Snowflake also offers QUALIFY, which filters on a window function without the wrapper, and it gets its own treatment later in the course. Wrapping is shown here because it works in every SQL dialect you will meet.

Is this not just LIMIT and OFFSET?

For that last query, yes, and pretending otherwise would be dishonest. Here is the same page with LIMIT and OFFSET.

Query

SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
ORDER BY SALARY DESC, EMP_ID
LIMIT 3 OFFSET 3;

Result3 rows · 2 columns

FIRST_NAME	SALARY
Marcus	96500.00
Priya	96500.00
Grace	92000.00

The same three people, in the same order, with less typing. If that is the whole requirement, use LIMIT and OFFSET.

The difference is not which one is shorter. It is what each one can express.

	LIMIT / OFFSET	ROW_NUMBER
Works on	the finished result as a whole	each partition independently
“The top three overall”	yes	yes
“The top three per department”	no — there is nothing to apply it per group	yes, with PARTITION BY
The number itself	not available to you	a column you can return, compare or filter on

The row about the top three per department is the one that matters, and doing something per group is what we try next.

Now let's keep one row per group

Here is the reason to learn this function. “The most recent order for each customer” is awkward to write with aggregates, because MAX(ORDER_DATE) gives us the date but not the row it came from. With ROW_NUMBER it is three steps: partition by the group, order by what “latest” means, keep number one.

Query

SELECT CUSTOMER_ID, ORDER_ID, ORDER_DATE, AMOUNT
FROM (SELECT CUSTOMER_ID, ORDER_ID, ORDER_DATE, AMOUNT,
             ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID
                                ORDER BY ORDER_DATE DESC, ORDER_ID DESC) AS RN
      FROM ORDERS) R
WHERE RN = 1
ORDER BY CUSTOMER_ID;

Result8 rows · 4 columns

CUSTOMER_ID	ORDER_ID	ORDER_DATE	AMOUNT
101	1040	2024-10-16	9640.00
102	1039	2024-09-29	1080.00
103	1031	2024-05-10	4025.00
104	1036	2024-08-02	2540.00
105	1033	2024-06-13	6900.00
106	1041	2024-11-05	NULL
107	1037	2024-08-25	13100.00
109	1038	2024-09-11	5930.00

One row per customer. The window’s ORDER BY carries ORDER_ID DESC as a tiebreak, so two orders on the same date could not leave the choice to chance.

Two things in that grid are worth stopping on. Customer 106’s latest order has a NULL AMOUNT, and ROW_NUMBER does not care, because it is ordering on ORDER_DATE and not summing anything. And the CUSTOMER_ID column runs 101 to 107 and then jumps to 109. A customer with no orders cannot appear in a query over ORDERS.

So is one row per customer the right number of rows? Let's count the customers in ORDERS directly.

Query

SELECT COUNT(DISTINCT CUSTOMER_ID) AS CUSTOMERS_WITH_ORDERS, COUNT(*) AS TOTAL_ORDERS
FROM ORDERS;

Result1 row · 2 columns

CUSTOMERS_WITH_ORDERS	TOTAL_ORDERS
8	41

CUSTOMERS_WITH_ORDERS matches the number of rows the one-row-per-customer query gave us, out of 41 orders in total. The pattern kept exactly one row per customer and lost none of them.

Where this pattern goes wrong in production

This is the deduplication pattern, and the dangerous version of it is RN = 1 with a window ORDER BY that can tie. You then keep one arbitrary row out of each tied set, the query succeeds, the row count looks right, and which rows survived is not something you specified.

Three steps, every time

Nearly every useful ROW_NUMBER query is the same shape. Writing it in this order makes the awkward parts obvious before you run it.

1. Decide the group. One number per table, or per customer, per department, per day? That is PARTITION BY — and leaving it out is a decision too.
2. Decide the order, completely. Not just the column you care about, but a tiebreak that cannot tie. If you cannot name one, the result is not repeatable.
3. Decide where the filter goes. Never in the same WHERE — wrap the query, or use QUALIFY.

Number them, then keep the numbers you want. That is the whole function.

Key points

• ROW_NUMBER() takes no arguments and aggregates nothing. It assigns consecutive numbers down the rows — no gaps, and never the same number twice.
• It requires an ORDER BY inside OVER. An empty OVER () is a compilation error, not a different answer.
• PARTITION BY restarts the numbering in every group, so each partition’s last number equals its row count.
• NULL is an ordinary partition key: a row with no DEPT_ID gets its own partition and is numbered 1 in it.
• On a tie, ROW_NUMBER must still give the two rows different numbers — so if your ORDER BY does not distinguish them, the query did not decide the outcome. Add a tiebreak.
• You cannot filter on a window function in WHERE: WHERE runs before the function does. Wrap the query, or use QUALIFY.
• Once the number is a column, any predicate works on it — <= 3 for a top-n, BETWEEN for a page in the middle, = 1 for one row per group.
• LIMIT and OFFSET can do the same job on a whole result, but they cannot do it per group. That is the reason ROW_NUMBER exists.
• “Number them, then keep number one” is the latest-row-per-key pattern, and it is the one you will write most often.

Next in this series

In the next post we will look at RANK and DENSE_RANK, two functions that handle the tie on this page completely differently. They give tied rows the same number, and then disagree with each other about what the next row gets.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode, including both error messages, which are Snowflake's own text and not paraphrased. The EMPLOYEES and ORDERS data is invented demo data created by that script. No performance or benchmark claims are made anywhere in this series. Function behaviour follows the Snowflake ROW_NUMBER documentation.