Snowflake SQL Tutorial 53 - LAST_VALUE in Snowflake

Snowflake SQL · Episode 53

In this post we are going to cover LAST_VALUE in Snowflake, together with FIRST_VALUE and NTH_VALUE.

FIRST_VALUE reaches to the start of the window and hands us a value from that row. LAST_VALUE reaches to the end of it. NTH_VALUE reaches to whichever position we ask for.

There is a very famous warning about LAST_VALUE: that it gives you the current row instead of the last one. It is true in several databases and it is not what Snowflake does. We will show that three different ways, and then write the frame that makes it happen anyway.

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

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

On this page
  1. Let's create some sample data
  2. Let's see what the partitions look like first
  3. Let's start with FIRST_VALUE and take the top of the window
  4. Now let's try LAST_VALUE at the other end
  5. Can we prove that twice more?
  6. So where is the bug? Let's write the frame ourselves
  7. Can FIRST_VALUE fall into the same trap?
  8. Now let's change the frame on purpose
  9. Let's take the easy way out and reverse the ORDER BY
  10. Now let's try NTH_VALUE for any position we like
  11. Let's finish with both ends at once, on a real table

Let's create some sample data

Most of this post runs against EMPLOYEES, partitioned by department. The script below creates the table and puts eight rows into it.

Run it once in a worksheet. At the end we will also use the shared ORDERS table, which Episode 52 sets up in full.

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

-- Grace's DEPT_ID is NULL on purpose. PARTITION BY DEPT_ID gives her a
-- partition of her own, which is the single-row window every function on this
-- page has to cope with.
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');

One employee has no DEPT_ID at all. That is on purpose. PARTITION BY DEPT_ID gives her a partition of her own, and a one-row window is the thing every function on this page has to cope with.

Let's see what the partitions look like first

Before we reach into a window it helps to know what the windows look like. That is a plain GROUP BY, with no window functions involved at all.

Query
SELECT DEPT_ID, COUNT(*) AS HEADCOUNT, MAX(SALARY) AS TOP_IN_DEPT, MIN(SALARY) AS BOTTOM_IN_DEPT
FROM EMPLOYEES GROUP BY DEPT_ID ORDER BY DEPT_ID NULLS LAST;
Result4 rows · 4 columns
DEPT_IDHEADCOUNTTOP_IN_DEPTBOTTOM_IN_DEPT
103131000.0096500.00
202104000.0087250.00
30296500.0071400.00
NULL192000.0092000.00

Four partitions. Three of them hold more than one person and one holds a single row, the employee whose DEPT_ID is NULL. She gets a partition to herself, because PARTITION BY groups nulls together exactly as GROUP BY does.

Keep TOP_IN_DEPT and BOTTOM_IN_DEPT in view. Those eight cells are what the window functions have to reproduce, and this is the department summary we will check everything against.

Let's start with FIRST_VALUE and take the top of the window

FIRST_VALUE does what the name says. It returns the value of its argument from the first row of the window.

So let's order the window by salary descending. The first row of each department is then its highest earner.

Query
SELECT DEPT_ID, FIRST_NAME, SALARY,
       FIRST_VALUE(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS TOP_IN_DEPT
FROM EMPLOYEES
ORDER BY DEPT_ID NULLS LAST, SALARY DESC;
Result8 rows · 4 columns
DEPT_IDFIRST_NAMESALARYTOP_IN_DEPT
10Daniel131000.00131000.00
10Aisha118000.00131000.00
10Marcus96500.00131000.00
20Lena104000.00104000.00
20Tomas87250.00104000.00
30Priya96500.0096500.00
30Sofia71400.0096500.00
NULLGrace92000.0092000.00

TOP_IN_DEPT is constant inside each department and it is repeated on every row of it. That is the whole point of a window function rather than a GROUP BY. The aggregate-like answer arrives beside each row instead of replacing the rows.

Now compare that column against the TOP_IN_DEPT column of the department summary we built with GROUP BY. Same four numbers, worked out two completely different ways. One used MAX over groups. This one reached for the first row of an ordered window.

A difference worth keeping straight

That equivalence only holds because the window is ordered by the same column the aggregate is taken over. FIRST_VALUE(SALARY) over ORDER BY HIRE_DATE is the earliest hire’s salary, which has nothing to do with MAX(SALARY). FIRST_VALUE answers “what was the value on the first row”, not “what was the largest value”. That is why it can fetch a column you are not sorting on at all.

Now let's try LAST_VALUE at the other end

There is a very well-known warning about LAST_VALUE, repeated in most SQL tutorials and true in several other databases. It says that LAST_VALUE written plainly does not give you the last row of the partition at all. It gives you the current row.

So let's write it plainly and see. Same partition, same ordering as the FIRST_VALUE query, no frame written out.

Query
SELECT DEPT_ID, FIRST_NAME, SALARY,
       LAST_VALUE(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS BOTTOM_IN_DEPT
FROM EMPLOYEES
ORDER BY DEPT_ID NULLS LAST, SALARY DESC;
Result8 rows · 4 columns
DEPT_IDFIRST_NAMESALARYBOTTOM_IN_DEPT
10Daniel131000.0096500.00
10Aisha118000.0096500.00
10Marcus96500.0096500.00
20Lena104000.0087250.00
20Tomas87250.0087250.00
30Priya96500.0071400.00
30Sofia71400.0071400.00
NULLGrace92000.0092000.00

That is the bottom of each partition, not the current row. Read the first department. All of its rows carry the same number, and it is the lowest salary in that department. It is the BOTTOM_IN_DEPT value from the department summary. The famous bug is not here.

The reason is the window frame, and this is the one thing to carry away from this post. A window function with an ORDER BY does not necessarily see the whole partition. It sees a frame inside it.

For FIRST_VALUE, LAST_VALUE and NTH_VALUE, Snowflake’s default frame is the entire partition. That is the same as writing ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. In the ANSI standard, and in the databases the warning comes from, the default stops at the current row instead, and that is precisely what produces the bug there.

That is a claim we can test, so let's test it. We write the full frame out and see whether anything changes.

Query — with the whole partition written out
SELECT DEPT_ID, FIRST_NAME, SALARY,
       LAST_VALUE(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC
                                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
         AS BOTTOM_IN_DEPT
FROM EMPLOYEES
ORDER BY DEPT_ID NULLS LAST, SALARY DESC;
Result8 rows · 4 columns
DEPT_IDFIRST_NAMESALARYBOTTOM_IN_DEPT
10Daniel131000.0096500.00
10Aisha118000.0096500.00
10Marcus96500.0096500.00
20Lena104000.0087250.00
20Tomas87250.0087250.00
30Priya96500.0071400.00
30Sofia71400.0071400.00
NULLGrace92000.0092000.00

Identical column. And rather than compare two grids by eye, let's put both expressions in one SELECT so the two columns sit side by side on every row.

Query
SELECT DEPT_ID, FIRST_NAME, SALARY,
       LAST_VALUE(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS PLAIN,
       LAST_VALUE(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC
                                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
         AS WITH_FULL_FRAME
FROM EMPLOYEES
ORDER BY DEPT_ID NULLS LAST, SALARY DESC;
Result8 rows · 5 columns
DEPT_IDFIRST_NAMESALARYPLAINWITH_FULL_FRAME
10Daniel131000.0096500.0096500.00
10Aisha118000.0096500.0096500.00
10Marcus96500.0096500.0096500.00
20Lena104000.0087250.0087250.00
20Tomas87250.0087250.0087250.00
30Priya96500.0071400.0071400.00
30Sofia71400.0071400.0071400.00
NULLGrace92000.0092000.0092000.00

PLAIN and WITH_FULL_FRAME agree on every row. In Snowflake, for these functions, writing the full frame out changes nothing. It only makes the behaviour visible to whoever reads the query next.

Can we prove that twice more?

Two grids agreeing is the kind of thing a reader is entitled to be suspicious of, especially when it contradicts what they have been told. So let's count it instead of eyeballing it.

Query
SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(PLAIN = WITH_FULL_FRAME)  AS ROWS_WHERE_THEY_AGREE,
       COUNT_IF(PLAIN <> WITH_FULL_FRAME) AS ROWS_WHERE_THEY_DIFFER
FROM (
  SELECT LAST_VALUE(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS PLAIN,
         LAST_VALUE(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
           AS WITH_FULL_FRAME
  FROM EMPLOYEES);
Result1 row · 3 columns
ROWS_TOTALROWS_WHERE_THEY_AGREEROWS_WHERE_THEY_DIFFER
880

COUNT_IF counts the rows where its predicate is true. Every row agrees, and none differ.

And the third way. We treat the two results as sets and subtract one from the other.

Query — MINUS between the two versions
SELECT FIRST_NAME, LAST_VALUE(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC)
FROM EMPLOYEES
MINUS
SELECT FIRST_NAME, LAST_VALUE(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC
                                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM EMPLOYEES;
Result0 rows · 2 columns
FIRST_NAMELAST_VALUE(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC)

No rows. So there is no (name, value) pair the plain version produces that the fully-framed version does not. Three different angles, one answer: plain LAST_VALUE in Snowflake returns the last row of the partition.

How to settle this kind of question yourself

This is also a decent template for checking any claim about SQL semantics. Put the two expressions on one row and look, count the rows where they differ, then subtract the results as sets. The second and third are the ones you can run against a table too wide and too long to read.

So where is the bug? Let's write the frame ourselves

The famous behaviour is real. It is just not the default, so we have to ask for it.

The frame people habitually write is a cumulative one: from the start of the partition up to the current row. Let's write it with RANGE, which is what an ORDER BY implies by default.

Query — this one fails
SELECT DEPT_ID, FIRST_NAME, SALARY,
       LAST_VALUE(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC
                                RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS X
FROM EMPLOYEES;
Query failed — Snowflake returned an error
002303 (0A000): SQL compilation error: error line 3 at position 32
Cumulative window frame unsupported for function LAST_VALUE

Cumulative window frame unsupported for function LAST_VALUE. It is a compilation error, so nothing runs and nothing wrong comes back. Snowflake has declined to answer a question whose answer would be useless.

Now let's write the same frame with ROWS instead of RANGE.

Query
SELECT DEPT_ID, FIRST_NAME, SALARY,
       LAST_VALUE(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC
                                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS X
FROM EMPLOYEES
ORDER BY DEPT_ID NULLS LAST, SALARY DESC;
Result8 rows · 4 columns
DEPT_IDFIRST_NAMESALARYX
10Daniel131000.00131000.00
10Aisha118000.00118000.00
10Marcus96500.0096500.00
20Lena104000.00104000.00
20Tomas87250.0087250.00
30Priya96500.0096500.00
30Sofia71400.0071400.00
NULLGrace92000.0092000.00

There is the famous answer. Every row returns its own salary.

With the frame written out in the query itself it is not mysterious at all. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is a window that ends at the current row, so the last row of everything up to me is, inevitably, me. The function is behaving perfectly. The frame was the wrong question.

Let's put both versions on one grid to make the contrast permanent.

Query
SELECT DEPT_ID, FIRST_NAME, SALARY,
       LAST_VALUE(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS DEFAULT_FRAME,
       LAST_VALUE(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC
                                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
         AS CUMULATIVE_ROWS
FROM EMPLOYEES
ORDER BY DEPT_ID NULLS LAST, SALARY DESC;
Result8 rows · 5 columns
DEPT_IDFIRST_NAMESALARYDEFAULT_FRAMECUMULATIVE_ROWS
10Daniel131000.0096500.00131000.00
10Aisha118000.0096500.00118000.00
10Marcus96500.0096500.0096500.00
20Lena104000.0087250.00104000.00
20Tomas87250.0087250.0087250.00
30Priya96500.0071400.0096500.00
30Sofia71400.0071400.0071400.00
NULLGrace92000.0092000.0092000.00

DEFAULT_FRAME is the bottom of the department on every row. CUMULATIVE_ROWS is the row’s own salary on every row. The two columns agree on exactly the rows that are the bottom of their own department, and differ everywhere else.

Query — and counted
SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(DEFAULT_FRAME <> CUMULATIVE_ROWS) AS ROWS_WHERE_THEY_DIFFER,
       COUNT_IF(CUMULATIVE_ROWS = SALARY)         AS ROWS_WHERE_CUMULATIVE_IS_THIS_ROW
FROM (
  SELECT SALARY,
         LAST_VALUE(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS DEFAULT_FRAME,
         LAST_VALUE(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
           AS CUMULATIVE_ROWS
  FROM EMPLOYEES);
Result1 row · 3 columns
ROWS_TOTALROWS_WHERE_THEY_DIFFERROWS_WHERE_CUMULATIVE_IS_THIS_ROW
848

Read that row left to right. 8 rows in total, 4 of them where the two frames disagree, and ROWS_WHERE_CUMULATIVE_IS_THIS_ROW equal to the row count. The cumulative column is the current row’s own salary on every single row, not just on the four where it diverges.

How to recognise it in your own SQL

If you meet this bug in Snowflake, you wrote a cumulative ROWS frame. That is the whole diagnosis. The frame is often copied in from a tutorial written for another database, or inherited from a named WINDOW clause shared with a running total, where a cumulative frame is exactly right and for LAST_VALUE is exactly wrong.

Can FIRST_VALUE fall into the same trap?

It cannot, and the reason is worth a sentence. Every cumulative frame starts at the first row of the partition. So the first row of the frame is the first row of the partition no matter where the frame ends. Shrinking the far end cannot move the near end.

Which also means FIRST_VALUE is allowed the cumulative RANGE frame that LAST_VALUE was refused. Let's try it.

Query
SELECT DEPT_ID, FIRST_NAME, SALARY,
       FIRST_VALUE(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC
                                 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS X
FROM EMPLOYEES
ORDER BY DEPT_ID NULLS LAST, SALARY DESC;
Result8 rows · 4 columns
DEPT_IDFIRST_NAMESALARYX
10Daniel131000.00131000.00
10Aisha118000.00131000.00
10Marcus96500.00131000.00
20Lena104000.00104000.00
20Tomas87250.00104000.00
30Priya96500.0096500.00
30Sofia71400.0096500.00
NULLGrace92000.0092000.00

Accepted, and the column is the same one plain FIRST_VALUE gave us. Now the ROWS variant, which was the frame that broke LAST_VALUE.

Query
SELECT DEPT_ID, FIRST_NAME, SALARY,
       FIRST_VALUE(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS X
FROM EMPLOYEES
ORDER BY DEPT_ID NULLS LAST, SALARY DESC;
Result8 rows · 4 columns
DEPT_IDFIRST_NAMESALARYX
10Daniel131000.00131000.00
10Aisha118000.00131000.00
10Marcus96500.00131000.00
20Lena104000.00104000.00
20Tomas87250.00104000.00
30Priya96500.0096500.00
30Sofia71400.0096500.00
NULLGrace92000.0092000.00

The same column again. Three different frames, one answer. FIRST_VALUE is the quietly safe one of the pair, and LAST_VALUE is the one to think about.

Now let's change the frame on purpose

None of this makes frames a trap to avoid. A frame is how we say which rows the function may look at, and the answer changing when the frame changes is the feature, not the bug.

Here is a sliding three-row window: the row before, this row, and the row after.

Query
SELECT DEPT_ID, FIRST_NAME, SALARY,
       LAST_VALUE(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC
                                ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS X
FROM EMPLOYEES
ORDER BY DEPT_ID NULLS LAST, SALARY DESC;
Result8 rows · 4 columns
DEPT_IDFIRST_NAMESALARYX
10Daniel131000.00118000.00
10Aisha118000.0096500.00
10Marcus96500.0096500.00
20Lena104000.0087250.00
20Tomas87250.0087250.00
30Priya96500.0071400.00
30Sofia71400.0071400.00
NULLGrace92000.0092000.00

Now LAST_VALUE returns the next row’s salary, because in a window of three rows ending one row below the current one, that genuinely is the last row.

And on the bottom row of each department there is no next row. The frame stops short and the function returns that row’s own value.

One more, and it shows how differently RANGE behaves from ROWS. Same sliding idea, but measured in salary rather than in rows.

Query
SELECT DEPT_ID, FIRST_NAME, SALARY,
       LAST_VALUE(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC
                                RANGE BETWEEN 10000 PRECEDING AND 10000 FOLLOWING) AS X
FROM EMPLOYEES
ORDER BY DEPT_ID NULLS LAST, SALARY DESC;
Result8 rows · 4 columns
DEPT_IDFIRST_NAMESALARYX
10Daniel131000.00131000.00
10Aisha118000.00118000.00
10Marcus96500.0096500.00
20Lena104000.00104000.00
20Tomas87250.0087250.00
30Priya96500.0096500.00
30Sofia71400.0071400.00
NULLGrace92000.0092000.00

Every row returns its own salary, and this time it has nothing to do with cumulative frames. RANGE offsets are measured in the ORDER BY column’s own units, so this frame means every row whose salary is within 10,000 of mine.

Read the SALARY column department by department and you can see why. Inside each department of this table no two salaries are that close. So every frame contains exactly one row, and the last row of a one-row window is that row.

ROWS and RANGE are not two spellings of one thing

ROWS counts rows. RANGE compares values. The same offsets written with the two keywords are different questions, and on data with duplicate ordering values they return different answers even when the offsets look the same. That comparison gets a whole episode of its own shortly.

Let's take the easy way out and reverse the ORDER BY

Here is what to write in practice, and it sidesteps every frame on this page. If we want the bottom of the partition, we do not reach for the end of the window. We turn the window around and reach for its start.

Query
SELECT DEPT_ID, FIRST_NAME, SALARY,
       FIRST_VALUE(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY SALARY ASC) AS BOTTOM_IN_DEPT
FROM EMPLOYEES
ORDER BY DEPT_ID NULLS LAST, SALARY DESC;
Result8 rows · 4 columns
DEPT_IDFIRST_NAMESALARYBOTTOM_IN_DEPT
10Daniel131000.0096500.00
10Aisha118000.0096500.00
10Marcus96500.0096500.00
20Lena104000.0087250.00
20Tomas87250.0087250.00
30Priya96500.0071400.00
30Sofia71400.0071400.00
NULLGrace92000.0092000.00

FIRST_VALUE with ORDER BY SALARY ASC returns the same four numbers plain LAST_VALUE gave us, which are the BOTTOM_IN_DEPT values from the department summary.

The statement’s own trailing ORDER BY still presents the rows highest-paid first. That is the independence of the two ORDER BY clauses doing something useful.

Both forms are correct in Snowflake. This one has the advantage that there is nothing to remember and nothing for a reviewer to second-guess. No frame, no default to know about, and no tutorial from another database to argue with.

Now let's try NTH_VALUE for any position we like

NTH_VALUE generalises both of the others. We give it a position and it returns that row of the window.

So the second-highest salary in each department is position 2 over a descending window.

Query
SELECT DEPT_ID, FIRST_NAME, SALARY,
       NTH_VALUE(SALARY, 2) OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
         AS SECOND_HIGHEST
FROM EMPLOYEES
ORDER BY DEPT_ID NULLS LAST, SALARY DESC;
Result8 rows · 4 columns
DEPT_IDFIRST_NAMESALARYSECOND_HIGHEST
10Daniel131000.00118000.00
10Aisha118000.00118000.00
10Marcus96500.00118000.00
20Lena104000.0087250.00
20Tomas87250.0087250.00
30Priya96500.0071400.00
30Sofia71400.0071400.00
NULLGrace92000.00NULL

Each multi-person department reports its own second-highest salary, repeated on every row of that department. And the single-row partition reports NULL. She is alone, so there is no second row to fetch, and NTH_VALUE says so instead of inventing something.

That query writes the full frame out. Let's leave the frame off and see whether the same default applies.

Query
SELECT DEPT_ID, FIRST_NAME, SALARY,
       NTH_VALUE(SALARY, 2) OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS SECOND_HIGHEST
FROM EMPLOYEES
ORDER BY DEPT_ID NULLS LAST, SALARY DESC;
Result8 rows · 4 columns
DEPT_IDFIRST_NAMESALARYSECOND_HIGHEST
10Daniel131000.00118000.00
10Aisha118000.00118000.00
10Marcus96500.00118000.00
20Lena104000.0087250.00
20Tomas87250.0087250.00
30Priya96500.0071400.00
30Sofia71400.0071400.00
NULLGrace92000.00NULL

The same column, including the null. The default window for all three of these functions is the whole partition.

Its one restriction

NTH_VALUE needs a constant position, and positions count from 1. So NTH_VALUE(X, 1) is FIRST_VALUE(X). If you find yourself wanting a position computed per row, what you want is a ranking function and a filter, not this.

Let's finish with both ends at once, on a real table

One last query, on ORDERS, the shared sample table from Level 3 that Episode 52 sets up in full.

We put FIRST_VALUE and LAST_VALUE on the same window, partitioned by customer and ordered by date. Then every order line carries the amount of that customer’s first order and their most recent one.

Query
SELECT CUSTOMER_ID, ORDER_ID, ORDER_DATE, AMOUNT,
       FIRST_VALUE(AMOUNT) OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_DATE
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
         AS FIRST_ORDER_AMOUNT,
       LAST_VALUE(AMOUNT)  OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_DATE
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
         AS LAST_ORDER_AMOUNT
FROM ORDERS
ORDER BY CUSTOMER_ID, ORDER_DATE
LIMIT 14;
Result14 rows · 6 columns · scroll →
CUSTOMER_IDORDER_IDORDER_DATEAMOUNTFIRST_ORDER_AMOUNTLAST_ORDER_AMOUNT
10110012023-01-124200.004200.009640.00
10110032023-02-146150.004200.009640.00
10110062023-03-225020.004200.009640.00
10110112023-06-088100.004200.009640.00
10110172023-09-165540.004200.009640.00
10110232023-12-276730.004200.009640.00
10110292024-04-067460.004200.009640.00
10110352024-07-188820.004200.009640.00
10110402024-10-169640.004200.009640.00
10210022023-01-28890.50890.501080.00
10210082023-04-191120.00890.501080.00
10210142023-07-29750.00890.501080.00
10210212023-11-24980.00890.501080.00
10210282024-03-211240.00890.501080.00

FIRST_ORDER_AMOUNT and LAST_ORDER_AMOUNT are constant within each customer’s block and change at the boundary between them.

The first customer’s block is complete inside the capture’s own LIMIT 14. It is 9 rows, and its first and last AMOUNT values are exactly what the two window columns report. The following block is cut off by that limit, so do not read it as that customer’s whole history.

That shape is immediately useful. First order beside latest order on one row gives us lifetime growth, reactivation and churn-risk reporting without a single subquery.

One thing to know before it surprises you

ORDERS contains exactly one row whose AMOUNT is NULL, deliberately, and it has been quietly relevant since Level 2.

Query
SELECT ORDER_ID, CUSTOMER_ID, ORDER_DATE, STATUS, AMOUNT
FROM ORDERS WHERE AMOUNT IS NULL;
Result1 row · 5 columns
ORDER_IDCUSTOMER_IDORDER_DATESTATUSAMOUNT
10411062024-11-05PENDINGNULL

These functions return a value, not a row. If the first or last row of a window happens to be an order like that one, what comes back is NULL. Not the nearest row that has an amount, and not zero. That is correct behaviour and it is rarely what a dashboard wants.

The switch that changes it

All three functions accept IGNORE NULLS (the default is RESPECT NULLS), which tells them to skip rows whose value is null and keep looking. It changes which row the value comes from, so decide which you mean: the last order, or the last order that had an amount.

Key points

  • FIRST_VALUE and LAST_VALUE return a value from the first and last row of the frame, not of the partition. That is why the default frame decides what they mean.
  • In Snowflake the default frame for FIRST_VALUE, LAST_VALUE and NTH_VALUE is the entire partition, not everything-up-to-the-current-row as in the ANSI standard. So plain LAST_VALUE correctly returns the bottom of the partition.
  • The famous “LAST_VALUE gives you the current row” bug is real, but you have to write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW yourself to get it. The last row of everything up to me is me.
  • The same cumulative frame written with RANGE is refused outright: cumulative window frame unsupported for function LAST_VALUE. A compilation error, so no wrong answer escapes.
  • FIRST_VALUE is immune, because every cumulative frame still starts at the first row of the partition.
  • Frames still work normally: a sliding frame makes LAST_VALUE return the next row, and a RANGE frame measured in the ordering column’s units can reduce the window to a single row.
  • The simplest fix is not to use LAST_VALUE at all. Reverse the ORDER BY inside OVER and use FIRST_VALUE. Same answer, nothing to remember.
  • NTH_VALUE(X, n) takes a constant position counted from 1, and returns NULL when the window is shorter than n rows.
  • All three return a value, so a null in the row they reach comes back as a null. IGNORE NULLS is the switch if you want the nearest row that has one.
Next in this series

Episode 54 — window frames properly. ROWS BETWEEN spelled out in full, what the default frame is for every other kind of window function, and running totals whose boundaries you choose rather than inherit.

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 compilation error, which is reproduced exactly as Snowflake returned it. 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 FIRST_VALUE documentation, the LAST_VALUE documentation and the NTH_VALUE documentation.

No comments:

Post a Comment

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

Subscribe to: Post Comments (Atom)