Snowflake SQL Tutorial 8 - TOP 3 Is NOT the Top 3 - LIMIT, TOP, FETCH & OFFSET in Snowflake

Snowflake SQL · Episode 8

In this post we are going to cover LIMIT, TOP, FETCH and OFFSET in Snowflake.

All four of these are ways of asking a query to give us back fewer rows. We tell Snowflake how many rows we want, and it stops there instead of handing us the whole table. OFFSET is the one that goes with them, and it lets us skip some rows before the counting starts.

We will start with some sample data, then take the three spellings one at a time, then put LIMIT and OFFSET together to build pages. At the end we will take the ORDER BY off a TOP 3 query and look very carefully at the three rows that come back.

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

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

On this page

1. Let's create some sample data
2. Let's start with LIMIT
3. What if we ask for more rows than the table has?
4. Now let's try TOP instead
5. Now let's try FETCH FIRST n ROWS ONLY
6. So which of the three should we write?
7. Now let's skip some rows with OFFSET
8. Now let's look at page two again
9. Now let's take the ORDER BY away

Let's create some sample data

Before we can ask for fewer rows we need a table with some rows in it. The script below creates the same EMPLOYEES table the series has used from the start.

Copy this into a Snowflake worksheet and run it once. If you followed the earlier posts you already have the table and you can skip it.

Sample data

-- Creates the EMPLOYEES table used by every episode in this series.
-- Run it once; every later episode can reuse it.
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
);

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');

The table is small on purpose. This post is about getting fewer rows back, and on a table we can hold in our head we can see exactly which rows went missing.

We only need two columns out of it. FIRST_NAME to recognise people by, and SALARY to sort on. Two employees share a salary, and that turns out to matter twice.

Let's start with LIMIT

LIMIT is the shortest way to ask for fewer rows. We write it at the end of the query, then the number of rows we want.

Let's take the query from the last post. It gives us names and salaries, highest salary first. Now we add LIMIT 3 to the end of it.

Query

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

Result3 rows · 2 columns

FIRST_NAME	SALARY
Daniel	131000.00
Aisha	118000.00
Lena	104000.00

Three rows instead of the whole table. Daniel on 131000.00, Aisha on 118000.00 and Lena on 104000.00. Those are the three highest earners.

Let's be careful about how that happened, because the last section of this post depends on it. Two clauses did that job, not one. ORDER BY decided who was at the top. LIMIT cut the list off after three rows. On its own, LIMIT has no opinion at all about which rows are worth keeping.

What if we ask for more rows than the table has?

That is a fair worry the first time you meet LIMIT. Let's ask for a hundred rows from a table that does not have a hundred rows in it.

Query

SELECT FIRST_NAME FROM EMPLOYEES ORDER BY FIRST_NAME LIMIT 100;

Result8 rows · 1 column

FIRST_NAME
Aisha
Daniel
Grace
Lena
Marcus
Priya
Sofia
Tomas

Eight names came back, which is everybody. No error, no complaint, and no padding rows to make the count up to 100. If there are fewer rows than we asked for, we get what there is.

That grid is also the full list of people this post works from, so we can check every later result against it. Eight employees, and the three-row grids below are three of these eight.

A detail worth filing away

The LIMIT documentation goes further than just tolerating a large number: “The values NULL, empty string ('') … are also accepted and are treated as ‘unlimited’.” That is genuinely useful when the limit arrives as a parameter and “no limit” is one of the values the caller may pass.

Now let's try TOP instead

TOP does the same job, and it goes somewhere else entirely. It sits immediately after the word SELECT, before the column list. There is nothing on the end of the query at all.

Let's ask for the same three rows that way.

Query

SELECT TOP 3 FIRST_NAME, SALARY FROM EMPLOYEES ORDER BY SALARY DESC;

Result3 rows · 2 columns

FIRST_NAME	SALARY
Daniel	131000.00
Aisha	118000.00
Lena	104000.00

Daniel, Aisha, Lena. The same three rows in the same order as the LIMIT version. The only thing that changed is where the keyword sits.

While we are here, have a look at the name of the keyword. The word TOP describes nothing about ranking. It takes the first n rows of whatever order the query already has. We will come back to that at the end of the post.

Now let's try FETCH FIRST n ROWS ONLY

The third spelling is the one in the SQL standard. It goes on the end of the query, where LIMIT goes, and it is wordier than both of the others.

Query

SELECT FIRST_NAME, SALARY FROM EMPLOYEES ORDER BY SALARY DESC FETCH FIRST 3 ROWS ONLY;

Result3 rows · 2 columns

FIRST_NAME	SALARY
Daniel	131000.00
Aisha	118000.00
Lena	104000.00

Daniel, Aisha, Lena. The same three rows, in the same order, for the third time.

We do not have to take three matching grids as proof, either. The documentation says it outright: “TOP n and LIMIT count are equivalent.”

FIRST or NEXT

Snowflake accepts FETCH with FIRST or NEXT, and with ROW or ROWS, so FETCH FIRST 3 ROWS ONLY and FETCH NEXT 3 ROWS ONLY are both valid in the same position. The next section uses the NEXT form, where it reads better.

So which of the three should we write?

They do the same job. So the choice is about the people who read our SQL, and about where that SQL has to run.

Spelling	Where it goes	Why you would pick it
LIMIT n	end of the query	Shortest, and the one most people recognise on sight
FETCH FIRST n ROWS ONLY	end of the query	ANSI standard — ports to other databases without editing
TOP n	immediately after SELECT	You will meet it in code somebody else wrote

There is one real difference, and it is not about style. The documentation says TOP’s n “must be a non-negative integer constant”. That means a literal. Not an expression, and not a parameter. So TOP is the least flexible of the three. If the number is generated or passed in from somewhere, reach for LIMIT or FETCH.

Now let's skip some rows with OFFSET

LIMIT gives us rows from the beginning. OFFSET moves where the beginning is. We skip some rows, then start counting. Read LIMIT 3 OFFSET 3 as skip three, then give me three.

Query

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

Result3 rows · 2 columns

FIRST_NAME	SALARY
Marcus	96500.00
Priya	96500.00
Grace	92000.00

Rows four, five and six of the sorted result. Marcus, Priya and Grace. That is page two, and this pair of numbers is how essentially all SQL pagination is built.

Now let's write the same thing the ANSI way

Query

SELECT FIRST_NAME, SALARY FROM EMPLOYEES ORDER BY SALARY DESC OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;

Result3 rows · 2 columns

FIRST_NAME	SALARY
Marcus	96500.00
Priya	96500.00
Grace	92000.00

The same three rows. Notice we wrote NEXT here rather than FIRST. We are asking for the next three after the skip, so that reads better. Snowflake accepts either word.

Page	Rows wanted	LIMIT form	ANSI form
one	first three	LIMIT 3	FETCH FIRST 3 ROWS ONLY
two	skip three, take three	LIMIT 3 OFFSET 3	OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY
three	skip six, take three	LIMIT 3 OFFSET 6	OFFSET 6 ROWS FETCH NEXT 3 ROWS ONLY

Now let's look at page two again

There is something in that page-two grid worth stopping on. Marcus is on 96500.00 and Priya is on 96500.00. The same salary.

Our sort key is SALARY DESC, and that clause cannot tell those two rows apart. Nothing in the query says which of them comes first. Snowflake put Marcus above Priya on this run. It was not asked to, and it did not promise to do it again.

On the page we just ran, that costs us nothing. Both rows landed together. The damage comes when a tied pair straddles a page boundary. Page one ends on one of them. Page two starts from an offset, sorts again, and may put the other one in that position. We read a row twice, or we never see it at all.

Watch out

Pagination built on a sort key that does not uniquely determine the order can duplicate rows or drop them, and no error is raised at any point. Every page is individually valid; the set of pages is not the table.

The fix is the second sort key from the last post. We add something unique after the salary, so there is no tie left for chance to resolve.

The one-key difference

-- Fragile: SALARY alone cannot separate Marcus from Priya,
-- so nothing in the query decides which of them page one ends on.
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
ORDER BY SALARY DESC
LIMIT 3 OFFSET 3;

-- Stable: EMP_ID is unique, so the order is fully determined and
-- every page boundary falls in the same place every time.
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
ORDER BY SALARY DESC, EMP_ID
LIMIT 3 OFFSET 3;

EMP_ID is unique, so SALARY DESC, EMP_ID gives every row exactly one possible position. The pages line up, and they keep lining up from one query to the next.

The habit

The rule generalises: if you are paginating, your ORDER BY must end in something unique. A primary key, or any column combination guaranteed distinct, is enough. It costs one identifier and removes an entire class of bug that is close to impossible to reproduce on demand.

Now let's take the ORDER BY away

This is the section the post exists for. We take the TOP 3 query from earlier, delete the ORDER BY, and change nothing else.

Before we look at the grid, what do we expect? Most people read TOP 3 and hear the top three.

Query

SELECT TOP 3 FIRST_NAME, SALARY FROM EMPLOYEES;

Result3 rows · 2 columns

FIRST_NAME	SALARY
Aisha	118000.00
Marcus	96500.00
Lena	104000.00

Read that carefully, because two of our assumptions just failed.

1. Daniel is missing. Daniel earns 131000.00 — more than anybody on this list. So these are not the three highest paid, and the keyword that says TOP returned a list without the top person in it.
2. It is not even sorted. Marcus on 96500.00 is sitting above Lena on 104000.00. The three rows are not in salary order in either direction.

Now compare that with the three grids earlier on this page. LIMIT 3, TOP 3 and FETCH FIRST 3 ROWS ONLY all gave us Daniel, Aisha and Lena. All three of them had an ORDER BY. The keyword never was the thing doing the ranking.

The documentation is blunt about what we get instead: “An ORDER BY clause is not required; however, without an ORDER BY clause, the results are non-deterministic because query results are not necessarily in any particular order.”

What non-deterministic actually costs you

Non-deterministic means we are not promised the same rows twice. The grid above is what this account returned on this run. Another run, a different warehouse size, or the same query after the table grows may hand us a different three. And it will look exactly as plausible as this one did.

So TOP 3 on its own does not mean the top three. It means three rows. If we want the top three, we have to say what top means, and ORDER BY is the only place we can say it.

Key points

• LIMIT n goes at the end of the query, TOP n goes immediately after SELECT, and the documentation says the two are equivalent.
• FETCH FIRST n ROWS ONLY is the ANSI spelling of the same thing; Snowflake also accepts NEXT in place of FIRST.
• LIMIT is a maximum, not a demand. Ask for 100 rows from an eight-row table and you get eight, with no error and no padding.
• TOP’s n must be a non-negative integer constant, which makes it the least flexible of the three spellings.
• OFFSET skips rows before the count starts, and LIMIT plus OFFSET is how pagination is built.
• If your sort key does not uniquely determine the order, pagination can repeat a row or lose one when a tie straddles a page boundary, with no error.
• The fix is a unique tiebreaker at the end of the ORDER BY, such as the primary key.
• None of these keywords rank anything. They take the first n rows of whatever order already exists.
• Without ORDER BY the results are non-deterministic, so TOP 3 means three rows, not the top three. The last grid on this page shows that by leaving out the highest earner in the table.

Next in this series

In the next post we will look at DISTINCT: removing duplicate rows from a result, what “duplicate” means across several columns at once, and why DISTINCT is not a function no matter where you put the brackets.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the script above. The EMPLOYEES data is invented demo data created by that script. No performance or benchmark claims are made anywhere in this series. The equivalence of TOP and LIMIT, and the constraint on TOP’s argument, are quoted from the TOP n documentation; the non-determinism sentence and the treatment of NULL as unlimited are quoted from the Snowflake LIMIT / FETCH documentation.