Snowflake SQL Tutorial 7 - ORDER BY in Snowflake - NULLS FIRST, NULLS LAST & Why NULLs Don't Always Sort Last

Snowflake SQL · Episode 7

In this post we are going to cover the ORDER BY clause in Snowflake.

ORDER BY is how we sort a result. We name a column to sort on and a direction, and the rows come back in that order. Without it a SELECT promises us nothing at all. The documentation is blunt about that: “Without an ORDER BY clause, the results returned by SELECT are an unordered set.”

We will start with one column and build it up from there. Sorting text and dates, sorting on two keys, sorting by column position, sorting by an alias, and sorting by something that is not in our output at all. Then we will deal with the NULLs, which do not stay where you would expect them to.

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

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

Let's create some sample data

Every query on this page runs against the EMPLOYEES table the series has used from the start. The script below creates it and puts eight rows into it.

Copy this into a Snowflake worksheet and run it once. Then you can run everything here yourself.

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

Four columns do all the work below. SALARY and HIRE_DATE for the simple sorts, LAST_NAME for the alphabetical one, and DEPT_ID for the part at the end that most people get wrong.

Keep DEPT_ID in mind in particular. It contains a NULL, and that NULL is the subject of the last two sections.

Let's start with the simplest sort

The clause is the two words followed by what we want to sort on. Nothing else is required.

Let's sort everybody by salary.

Query
SELECT FIRST_NAME, SALARY FROM EMPLOYEES ORDER BY SALARY;
Result8 rows · 2 columns
FIRST_NAMESALARY
Sofia71400.00
Tomas87250.00
Grace92000.00
Marcus96500.00
Priya96500.00
Lena104000.00
Aisha118000.00
Daniel131000.00

Lowest salary at the top, Sofia on 71400.00, rising to Daniel on 131000.00 at the bottom. That is ascending, and it is what we get when we do not say which direction we want.

There is an ASC keyword we may write, and almost nobody writes it, because it is already the default.

Now let's turn it round with DESC

Query
SELECT FIRST_NAME, SALARY FROM EMPLOYEES ORDER BY SALARY DESC;
Result8 rows · 2 columns
FIRST_NAMESALARY
Daniel131000.00
Aisha118000.00
Lena104000.00
Marcus96500.00
Priya96500.00
Grace92000.00
Tomas87250.00
Sofia71400.00

The same eight rows, turned over. Daniel is at the top now and Sofia is at the bottom.

The tie in both of those grids

Compare the middle of those two grids carefully. Marcus and Priya both earn 96500.00, and in both runs Marcus came out above Priya — the tie did not reverse when the sort did. Nothing guarantees that. ORDER BY orders rows by the keys you give it; for rows those keys cannot separate it promises nothing at all. If the order within a tie matters to you, add a second key that breaks it — EMP_ID is the usual choice.

Now let's sort text and dates

The same clause handles anything with a defined sort order. Let's try it on a text column.

Query
SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES ORDER BY LAST_NAME;
Result8 rows · 2 columns
FIRST_NAMELAST_NAME
LenaFischer
AishaKhan
GraceLin
SofiaMarino
TomasNovak
DanielO'Connor
PriyaRaman
MarcusReid

Fischer, Khan, Lin, Marino, Novak, O’Connor, Raman, Reid. Alphabetical order, as we asked for.

Now look at what is not in that output. SALARY is gone entirely, and the sort has nothing to do with it. The sort key and the column list are separate choices, and that gets its own section further down this page.

Now let's sort on a date

Query
SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES ORDER BY HIRE_DATE;
Result8 rows · 2 columns
FIRST_NAMEHIRE_DATE
Daniel2016-05-30
Lena2018-01-22
Aisha2019-03-11
Priya2020-11-17
Grace2021-04-19
Marcus2021-07-01
Tomas2022-09-05
Sofia2023-02-13

Ascending on a date means oldest first. Daniel, hired 2016-05-30, is the longest-serving employee in the table, and Sofia on 2023-02-13 is the newest. Add DESC and we would have the most recent hire first instead.

Dates sort chronologically rather than as text, which is the behaviour we want. It is not something to take for granted in every system you will meet.

Now let's sort by more than one column

We separate the keys with commas. Snowflake sorts by the first key, and it reaches for the second one only to break ties inside the first.

Let's read this one as a sentence before we run it. Group everybody by department, and within each department put the highest earner first.

Query
SELECT FIRST_NAME, DEPT_ID, SALARY FROM EMPLOYEES ORDER BY DEPT_ID, SALARY DESC;
Result8 rows · 3 columns
FIRST_NAMEDEPT_IDSALARY
Daniel10131000.00
Aisha10118000.00
Marcus1096500.00
Lena20104000.00
Tomas2087250.00
Priya3096500.00
Sofia3071400.00
GraceNULL92000.00

Department 10 arrives first with Daniel, Aisha and Marcus in descending salary order. Then department 20, then department 30, each sorted the same way inside.

Watch out

DESC applies only to the column it follows. DEPT_ID in that query is still ascending — the DESC belongs to SALARY alone. If you want both keys descending you have to write DESC twice. This is the single most common misreading of a multi-key sort.

Grace is at the bottom with a NULL DEPT_ID. Where she lands is the subject of the last two sections.

Can we sort by column position instead?

We can. Instead of naming the columns we may give their position in the SELECT list.

In the query above DEPT_ID is the second column and SALARY is the third, so this is meant to be the same sort.

Query
SELECT FIRST_NAME, DEPT_ID, SALARY FROM EMPLOYEES ORDER BY 2, 3 DESC;
Result8 rows · 3 columns
FIRST_NAMEDEPT_IDSALARY
Daniel10131000.00
Aisha10118000.00
Marcus1096500.00
Lena20104000.00
Tomas2087250.00
Priya3096500.00
Sofia3071400.00
GraceNULL92000.00

Identical row order. Compare it line by line against the previous grid.

These are called ordinals, and they are genuinely useful in a worksheet when we are sorting by a long expression we do not want to retype.

Why I would not ship them

Ordinals are compact and they are fragile. The number refers to a position, so the day somebody inserts a column into the SELECT list, your sort silently starts sorting by a different column. The query does not fail, does not warn, and does not sort by what you meant. Name the columns in anything you are keeping.

Can we sort by an alias?

We can do that too. MONTHLY below is a calculated column. It exists nowhere in the table and only for the life of this query, and ORDER BY can still refer to it by name.

Query
SELECT FIRST_NAME, SALARY / 12 AS MONTHLY FROM EMPLOYEES ORDER BY MONTHLY DESC;
Result8 rows · 2 columns
FIRST_NAMEMONTHLY
Daniel10916.66666667
Aisha9833.33333333
Lena8666.66666667
Marcus8041.66666667
Priya8041.66666667
Grace7666.66666667
Tomas7270.83333333
Sofia5950.00000000

Daniel on 10916.66666667 a month down to Sofia on 5950.00000000. That worked.

It is worth knowing why it worked, because the same trick fails in a WHERE clause. The answer is the order the clauses are evaluated in, which is not the order we write them in.

EvaluatedClauseDoes an alias from SELECT exist yet?
firstFROMno
thenWHEREno — which is why a WHERE clause that names MONTHLY fails
thenGROUP BY / HAVINGno
thenSELECTthis is where the alias is created
lastORDER BYyes — the alias already exists

WHERE runs before the SELECT list is built, so the name it would need has not been invented yet. ORDER BY runs after, so it has.

Understand the reason and you never have to memorise which clause allows it.

What to do instead

If you do need to filter on a calculated value, write the expression out again in the WHERE clause — test SALARY / 12 rather than MONTHLY — or wrap the query so the alias becomes a real column of an inner result. Both work; neither is a sign you have done something wrong.

Now let's sort by something that is not in our output

ORDER BY does not need the sort key to appear in our results. It does not even need the sort key to be a column.

Let's order the rows by the length of the first name, then alphabetically within each length. There is no length column anywhere in the output.

Query
SELECT FIRST_NAME, SALARY FROM EMPLOYEES ORDER BY LENGTH(FIRST_NAME), FIRST_NAME;
Result8 rows · 2 columns
FIRST_NAMESALARY
Lena104000.00
Aisha118000.00
Grace92000.00
Priya96500.00
Sofia71400.00
Tomas87250.00
Daniel131000.00
Marcus96500.00

Lena is first, because her name is the shortest. Then the five-letter names in alphabetical order, Aisha and Grace and Priya and Sofia and Tomas. Then Daniel and Marcus, the two six-letter names, also alphabetical.

Nothing on the page explains that order. The column that produced it was never selected.

That is a powerful feature and a real readability cost at the same time. A sort nobody reading the result can account for is a sort somebody will eventually “fix”. If the ordering logic matters to the reader, consider selecting the expression as well so the result explains itself.

Now let's see where the NULLs go

The last post showed a sort where the NULL came last, and it deliberately refused to turn that into a rule. Here is the evidence for the refusal.

First, ascending, with ASC written out so there is no doubt what we asked for.

Query
SELECT FIRST_NAME, DEPT_ID FROM EMPLOYEES ORDER BY DEPT_ID ASC;
Result8 rows · 2 columns
FIRST_NAMEDEPT_ID
Aisha10
Marcus10
Daniel10
Lena20
Tomas20
Priya30
Sofia30
GraceNULL

Grace, whose DEPT_ID is NULL, comes last.

Now let's run the same column, the same table and the same NULL the other way round.

Query
SELECT FIRST_NAME, DEPT_ID FROM EMPLOYEES ORDER BY DEPT_ID DESC;
Result8 rows · 2 columns
FIRST_NAMEDEPT_ID
GraceNULL
Priya30
Sofia30
Lena20
Tomas20
Aisha10
Marcus10
Daniel10

Grace is at the top. The NULL did not stay where it was and let the known values reverse around it. It moved with the sort direction, from one end of the result to the other.

The documentation says what the placement actually depends on: “When NULLS FIRST or NULLS LAST isn’t specified, the ordering of NULL values depends on the setting of the DEFAULT_NULL_ORDERING parameter and the sort order.”

Watch out

There are two dependencies in that sentence, and only one of them is visible in your query. The sort direction you can see. DEFAULT_NULL_ORDERING is a session parameter — somebody can change it, on a different warehouse or in a different tool, and your report quietly reorders itself with no change to the SQL.

One last thing: let's take the decision ourselves

The fix is to stop depending on either of those things and say which end we want. We add NULLS FIRST after the sort key.

Query
SELECT FIRST_NAME, DEPT_ID FROM EMPLOYEES ORDER BY DEPT_ID NULLS FIRST;
Result8 rows · 2 columns
FIRST_NAMEDEPT_ID
GraceNULL
Aisha10
Marcus10
Daniel10
Lena20
Tomas20
Priya30
Sofia30

Grace is at the top of an ascending sort. That is the position the default put her in only when the sort was descending.

Now let's ask for the other end.

Query
SELECT FIRST_NAME, DEPT_ID FROM EMPLOYEES ORDER BY DEPT_ID NULLS LAST;
Result8 rows · 2 columns
FIRST_NAMEDEPT_ID
Aisha10
Marcus10
Daniel10
Lena20
Tomas20
Priya30
Sofia30
GraceNULL

And she is at the bottom. Both of those are ascending sorts of the same column. The only difference is which end we asked the NULL to go to.

Written asWhere the NULL landsDepends on the session parameter?
ORDER BY DEPT_IDwhatever the default resolves toyes
ORDER BY DEPT_ID DESCwhatever the default resolves to, for that directionyes
ORDER BY DEPT_ID NULLS FIRSTfirst, alwaysno
ORDER BY DEPT_ID NULLS LASTlast, alwaysno

Two extra words, and the question stops being open. If the position of our NULLs is visible to anybody, on a dashboard or in an export or at the top of a page of results, let's write them.

Key points

  • Without ORDER BY, a SELECT returns an unordered set. Any order you see is a courtesy, not a guarantee.
  • ASC is the default and almost never written. DESC reverses the direction.
  • DESC applies only to the key it follows. Two descending keys need DESC written twice.
  • Extra keys separated by commas break ties inside the key before them. For rows no key can separate, no order is promised at all.
  • Text sorts alphabetically and dates chronologically. The sort key does not have to be a number.
  • Ordinals (ORDER BY 2, 3 DESC) work but refer to positions, so inserting a column into the SELECT list silently changes what you sort by.
  • You can ORDER BY an alias but not WHERE on one, because WHERE is evaluated before the SELECT list exists and ORDER BY after it.
  • The sort key does not have to appear in your output. ORDER BY LENGTH(FIRST_NAME) produces an order nothing on the page explains.
  • NULLs do not have a fixed home. The same column sorted ASC then DESC put the NULL at opposite ends.
  • Default NULL placement depends on the DEFAULT_NULL_ORDERING session parameter and the sort order. NULLS FIRST or NULLS LAST depends on neither.
Next in this series

In the next post we will cover LIMIT, TOP, FETCH and OFFSET. Asking for a few rows instead of all of them, and why every one of those keywords is close to meaningless without the ORDER BY on this page.

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 NULL ordering caveat and the DEFAULT_NULL_ORDERING parameter are quoted from the Snowflake ORDER BY documentation; the unordered-set sentence is quoted from the SELECT documentation.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.