Snowflake SQL Tutorial 48 - ORDER BY Inside OVER in Snowflake - Three Words That Turn a Total Into a Running Total

Snowflake SQL · Episode 48

In this post we are going to cover ORDER BY inside OVER in Snowflake.

We already know that SUM(SALARY) OVER () puts the same total on every row. Write ORDER BY SALARY between those brackets and the same SUM stops being a total and becomes a running total. Three words, and a completely different column.

We will see that happen first, and then find out why it happens. The answer is not the sorting. An ORDER BY inside OVER brings a window frame with it, and the frame is what changes the answer. After that we will look at what a tie does to it, keep the two ORDER BY clauses apart, and add PARTITION BY on top.

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

Episode 48 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 the total on every row
3. So why did three words do that?
4. Now let's keep the sort and replace the frame
5. Now let's see what a tie does to it
6. Now let's take the two ORDER BYs apart
7. Now let's add PARTITION BY on top
8. How to read any OVER clause

Let's create some sample data

Everything on this page runs against EMPLOYEES, the small table this series has been using since Episode 1. Paste this into a Snowflake worksheet and run it once.

Sample data

-- The EMPLOYEES table this episode runs against. It comes from Episode 1 and
-- every episode in this series reuses it, so you only set it 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
);

-- Look at Marcus and Priya. They have the same SALARY, and that single tie is
-- what the second half of this post is about.
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');

Before any window function, let's reduce the whole table to two numbers. Keep the second one in mind. It turns up again, several times, as the answer to a different question.

Query

SELECT COUNT(*) AS HEADCOUNT, SUM(SALARY) AS TOTAL_PAYROLL FROM EMPLOYEES;

Result1 row · 2 columns

HEADCOUNT	TOTAL_PAYROLL
8	796650.00

Eight people, and that is the total payroll. A plain SUM with no GROUP BY anywhere: one row, one number.

Let's start with the total on every row

Episode 46 gave us SUM(SALARY) OVER (), an aggregate that does not collapse the rows. Every row keeps its identity and gains the total beside it. Let's run it.

Query — empty OVER ()

SELECT FIRST_NAME, SALARY,
       SUM(SALARY) OVER () AS TOTAL
FROM EMPLOYEES
ORDER BY SALARY;

Result8 rows · 3 columns

FIRST_NAME	SALARY	TOTAL
Sofia	71400.00	796650.00
Tomas	87250.00	796650.00
Grace	92000.00	796650.00
Marcus	96500.00	796650.00
Priya	96500.00	796650.00
Lena	104000.00	796650.00
Aisha	118000.00	796650.00
Daniel	131000.00	796650.00

The same value on all eight rows, and it is the TOTAL_PAYROLL figure the plain SUM gave us. So far, nothing new.

Now let's add three words inside the brackets. Same function, same column, same table, same ORDER BY at the end of the query. The only change is ORDER BY SALARY between the parentheses.

Query — OVER (ORDER BY SALARY)

SELECT FIRST_NAME, SALARY,
       SUM(SALARY) OVER (ORDER BY SALARY) AS RUNNING_TOTAL
FROM EMPLOYEES
ORDER BY SALARY;

Result8 rows · 3 columns

FIRST_NAME	SALARY	RUNNING_TOTAL
Sofia	71400.00	71400.00
Tomas	87250.00	158650.00
Grace	92000.00	250650.00
Marcus	96500.00	443650.00
Priya	96500.00	443650.00
Lena	104000.00	547650.00
Aisha	118000.00	665650.00
Daniel	131000.00	796650.00

That is a different column entirely. Sofia is the lowest paid and gets her own salary back, 71,400.00. Tomas gets Sofia's plus his own, 158,650.00. Grace adds hers and reaches 250,650.00. It builds up down the grid, and the last row lands exactly on the total payroll.

Now let's put both of them in one query

Query — the same function twice, differing only inside the brackets

SELECT FIRST_NAME, SALARY,
       SUM(SALARY) OVER ()                 AS TOTAL,
       SUM(SALARY) OVER (ORDER BY SALARY)  AS RUNNING_TOTAL
FROM EMPLOYEES
ORDER BY SALARY;

Result8 rows · 4 columns

FIRST_NAME	SALARY	TOTAL	RUNNING_TOTAL
Sofia	71400.00	796650.00	71400.00
Tomas	87250.00	796650.00	158650.00
Grace	92000.00	796650.00	250650.00
Marcus	96500.00	796650.00	443650.00
Priya	96500.00	796650.00	443650.00
Lena	104000.00	796650.00	547650.00
Aisha	118000.00	796650.00	665650.00
Daniel	131000.00	796650.00	796650.00

TOTAL is flat. RUNNING_TOTAL climbs. One SELECT, one table, one function name, and three words of difference.

The last value of a running total should be the grand total. Let's have that checked rather than eyeballed.

Query — the largest running value

SELECT MAX(RUNNING_TOTAL) AS LAST_RUNNING_VALUE
FROM (SELECT SUM(SALARY) OVER (ORDER BY SALARY) AS RUNNING_TOTAL FROM EMPLOYEES);

Result1 row · 1 column

LAST_RUNNING_VALUE
796650.00

Why the queries also sort at the end

The ORDER BY SALARY at the end of these queries, outside the brackets, is there so the accumulation reads top to bottom on the page. It is not what creates the running total. We will take those two clauses apart later in this post and show that the one at the end does not change a single value.

So why did three words do that?

Here is the explanation, and it is the whole post. An ORDER BY inside OVER carries a default window frame with it.

A frame is the set of rows the function is allowed to look at for the row it is currently producing a value for. It is not the same thing as the partition. It is a window inside the partition, and it can move as we go down the rows.

What you write inside OVER	The frame you get	What the column looks like
nothing — OVER ()	the entire partition	one value, repeated on every row
ORDER BY SALARY	from the start of the partition up to and including this row	a running total
ORDER BY SALARY plus a frame you write yourself	whatever you wrote	whatever you asked for

Read the middle row again. From the start, up to and including this row. That sentence is the definition of a running total. We never asked for one. We asked for an order, and the frame came attached to it.

Let's write that default frame out by hand

If that really is the default frame, then writing it out ourselves should change nothing at all.

Query — the default frame, spelled out

SELECT FIRST_NAME, SALARY,
       SUM(SALARY) OVER (ORDER BY SALARY
                         RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
         AS RUNNING_TOTAL_EXPLICIT
FROM EMPLOYEES
ORDER BY SALARY;

Result8 rows · 3 columns

FIRST_NAME	SALARY	RUNNING_TOTAL_EXPLICIT
Sofia	71400.00	71400.00
Tomas	87250.00	158650.00
Grace	92000.00	250650.00
Marcus	96500.00	443650.00
Priya	96500.00	443650.00
Lena	104000.00	547650.00
Aisha	118000.00	665650.00
Daniel	131000.00	796650.00

Identical to the RUNNING_TOTAL column we got from OVER (ORDER BY SALARY), value for value.

Identical by eye is not worth much though, so let's ask Snowflake instead. MINUS gives back the rows in the first result that are not in the second one.

Query — MINUS between the implicit and explicit versions

SELECT FIRST_NAME, SUM(SALARY) OVER (ORDER BY SALARY) FROM EMPLOYEES
MINUS
SELECT FIRST_NAME, SUM(SALARY) OVER (ORDER BY SALARY
                                     RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM EMPLOYEES;

Result0 rows · 2 columns

FIRST_NAME	SUM(SALARY) OVER (ORDER BY SALARY)

No rows. There is nothing in the version we left implicit that the written-out version did not also produce. The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Now let's keep the sort and replace the frame

That MINUS tells us what the default frame is. On its own it still leaves the obvious reading open: maybe sorting is simply what makes a sum build up. So let's do the other half of the experiment. We keep the ORDER BY exactly where it is, and write a frame that covers everything.

Query — ORDER BY kept, frame widened to the whole partition

SELECT FIRST_NAME, SALARY,
       SUM(SALARY) OVER (ORDER BY SALARY
                         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
         AS TOTAL_AGAIN
FROM EMPLOYEES
ORDER BY SALARY;

Result8 rows · 3 columns

FIRST_NAME	SALARY	TOTAL_AGAIN
Sofia	71400.00	796650.00
Tomas	87250.00	796650.00
Grace	92000.00	796650.00
Marcus	96500.00	796650.00
Priya	96500.00	796650.00
Lena	104000.00	796650.00
Aisha	118000.00	796650.00
Daniel	131000.00	796650.00

The flat total is back, on every row, with ORDER BY SALARY still sitting inside the brackets. Same sort, different frame, different answer.

Why it took two queries

Two experiments, two different jobs. The MINUS shows that the default frame is the one we claim it is. This one shows that the sorting was never the cause. We need both halves, because either one on its own is a story that happens to fit.

Now let's see what a tie does to it

Here is the detail that catches people out. It only shows up when two rows are equal on the ordering column, so rather than claim this table has a tie, let's ask.

Query — which salaries are shared, and by whom

SELECT SALARY, COUNT(*) AS PEOPLE, LISTAGG(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 back, so there is exactly one tie in the table. Marcus and Priya, on the same salary. Now let's watch what the running total does when it reaches them.

Query — the default frame (RANGE)

SELECT FIRST_NAME, SALARY,
       SUM(SALARY) OVER (ORDER BY SALARY) AS RUNNING_RANGE
FROM EMPLOYEES
ORDER BY SALARY, FIRST_NAME;

Result8 rows · 3 columns

FIRST_NAME	SALARY	RUNNING_RANGE
Sofia	71400.00	71400.00
Tomas	87250.00	158650.00
Grace	92000.00	250650.00
Marcus	96500.00	443650.00
Priya	96500.00	443650.00
Lena	104000.00	547650.00
Aisha	118000.00	665650.00
Daniel	131000.00	796650.00

Marcus and Priya both read 443,650.00. Marcus did not get a smaller number for being first. The running total jumped over both of them at once.

That is the word RANGE in the default frame doing its job. RANGE defines the frame by value, not by position. So “everything up to and including this row” means everything up to and including this salary. Each of the two tied rows falls inside the other one's frame, and both get the same answer.

Let's swap one word and count rows instead

Query — the same frame bounds, written with ROWS

SELECT FIRST_NAME, SALARY,
       SUM(SALARY) OVER (ORDER BY SALARY
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
         AS RUNNING_ROWS
FROM EMPLOYEES
ORDER BY SALARY, FIRST_NAME;

Result8 rows · 3 columns

FIRST_NAME	SALARY	RUNNING_ROWS
Sofia	71400.00	71400.00
Tomas	87250.00	158650.00
Grace	92000.00	250650.00
Marcus	96500.00	347150.00
Priya	96500.00	443650.00
Lena	104000.00	547650.00
Aisha	118000.00	665650.00
Daniel	131000.00	796650.00

Now Marcus reads 347,150.00 and Priya reads 443,650.00. ROWS counts physical positions, so the tie is no longer special. Marcus’s frame stops at Marcus.

Let's put both columns in one grid, which is where the difference is easiest to see.

Query — RANGE and ROWS side by side

SELECT FIRST_NAME, SALARY,
       SUM(SALARY) OVER (ORDER BY SALARY) AS RUNNING_RANGE,
       SUM(SALARY) OVER (ORDER BY SALARY
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
         AS RUNNING_ROWS
FROM EMPLOYEES
ORDER BY SALARY, FIRST_NAME;

Result8 rows · 4 columns

FIRST_NAME	SALARY	RUNNING_RANGE	RUNNING_ROWS
Sofia	71400.00	71400.00	71400.00
Tomas	87250.00	158650.00	158650.00
Grace	92000.00	250650.00	250650.00
Marcus	96500.00	443650.00	347150.00
Priya	96500.00	443650.00	443650.00
Lena	104000.00	547650.00	547650.00
Aisha	118000.00	665650.00	665650.00
Daniel	131000.00	796650.00	796650.00

Seven of the eight rows agree exactly. The tied pair is the only place the two columns disagree, and only on the first of the two. That single cell is the entire difference between ROWS and RANGE on this data.

The trap

RANGE is the default. If your ordering column has duplicates and you wanted a strictly row-by-row accumulation, you did not get one — and the query ran, returned the right number of rows, and said nothing. Write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when that is what you mean.

Episode 55 is about nothing but ROWS versus RANGE, so this is the preview rather than the full treatment.

Now let's take the two ORDER BYs apart

Every query so far has had two ORDER BY clauses, and they are unrelated to each other.

• The one inside the brackets decides how the running total builds up.
• The one at the end of the query decides what order the rows are handed back in.

To show that they really are independent, let's ask for the same running total by salary and get it back in alphabetical order instead.

Query — accumulate by SALARY, return by FIRST_NAME

SELECT FIRST_NAME, SALARY,
       SUM(SALARY) OVER (ORDER BY SALARY) AS RUNNING_BY_SALARY
FROM EMPLOYEES
ORDER BY FIRST_NAME;

Result8 rows · 3 columns

FIRST_NAME	SALARY	RUNNING_BY_SALARY
Aisha	118000.00	665650.00
Daniel	131000.00	796650.00
Grace	92000.00	250650.00
Lena	104000.00	547650.00
Marcus	96500.00	443650.00
Priya	96500.00	443650.00
Sofia	71400.00	71400.00
Tomas	87250.00	158650.00

The rows arrive alphabetically, and every RUNNING_BY_SALARY value is the same one that person had when we read the grid in salary order. Aisha on 665,650.00, Sofia on 71,400.00. The ORDER BY at the end rearranged the rows. It did not redo the arithmetic.

Worth remembering when a report looks broken

This is why a running total can look wrong when it is not. Read down a column ordered by something other than the window’s own ORDER BY and the numbers will not climb. They are still correct. They are just no longer in the order they were accumulated in.

Now let's add PARTITION BY on top

This is the form we will actually write. PARTITION BY decides where the running total starts over, and ORDER BY decides the order it runs in. Episode 47 covered the first half, and the two go in the same set of brackets.

The query below also carries a second window function with no ORDER BY at all, so each row shows its department total beside the running one. That is the number each run is supposed to finish on.

Query — a running total per department, with the department total beside it

SELECT FIRST_NAME, DEPT_ID, SALARY,
       SUM(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY SALARY) AS RUNNING_IN_DEPT,
       SUM(SALARY) OVER (PARTITION BY DEPT_ID)                 AS DEPT_TOTAL
FROM EMPLOYEES
ORDER BY DEPT_ID NULLS LAST, SALARY;

Result8 rows · 5 columns

FIRST_NAME	DEPT_ID	SALARY	RUNNING_IN_DEPT	DEPT_TOTAL
Marcus	10	96500.00	96500.00	345500.00
Aisha	10	118000.00	214500.00	345500.00
Daniel	10	131000.00	345500.00	345500.00
Tomas	20	87250.00	87250.00	191250.00
Lena	20	104000.00	191250.00	191250.00
Sofia	30	71400.00	71400.00	167900.00
Priya	30	96500.00	167900.00	167900.00
Grace	NULL	92000.00	92000.00	92000.00

Four partitions, because DEPT_ID has four distinct values in this table and NULL is one of them. Grace has no department, so she is a partition on her own.

Read each run down to its last row and compare it with DEPT_TOTAL: 345,500.00, 191,250.00, 167,900.00 and 92,000.00. Every run finishes on its own partition total, and no run ever sees a row from another department.

What happened to the tie

Marcus and Priya are still tied on salary, but they are in different departments now. The tie is split across two partitions, so the RANGE behaviour we just looked at has nothing to act on. A tie only matters inside a partition.

How to read any OVER clause

Once a window specification has more than one clause in it, the fastest way to work out what a query will return is to take the brackets apart in this order.

1. PARTITION BY — which rows are in the same group? Nothing crosses a partition boundary, ever.
2. ORDER BY — in what order are the rows considered inside that group? If this is absent, stop: the frame is the whole partition and the answer is a single repeated value.
3. The frame — written out, or the default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW that the ORDER BY brought with it.
4. ROWS or RANGE — if the ordering column has duplicates, this decides what happens on the tie.

And the clause after the closing bracket, at the end of the query, is not part of that list at all. It only decides what order we read the finished answer in.

Key points

• ORDER BY inside OVER does not only sort — it brings a default window frame with it, and that frame is what changes the answer.
• The default frame under an ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — “from the start up to and including this row”, which is the definition of a running total.
• Writing that frame out by hand returns an identical column, and a MINUS between the two versions comes back empty.
• Keep the ORDER BY but widen the frame to the whole partition and the flat total returns. The sort was never the cause.
• RANGE defines the frame by value. Tied rows are inside each other’s frames, so a running total jumps over the whole tie at once and both rows get the same number.
• ROWS defines the frame by position, so each tied row stops at itself. On this table that is the only cell where the two disagree.
• The ORDER BY inside the brackets drives the arithmetic; the one at the end of the query only arranges the output. Changing the second one does not change a single value.
• PARTITION BY with ORDER BY gives a running total that restarts in every group and finishes on that group’s own total.

Next in this series

In the next post we will look at ROW_NUMBER, the first window function in this course that is not an aggregate at all. It adds nothing up. It numbers the rows — and it refuses to run without an ORDER BY.

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 the empty MINUS result. The EMPLOYEES data is invented demo data created by the setup script above. No performance or benchmark claims are made anywhere in this series. Frame and clause behaviour follows the Snowflake window function documentation.