topic Window function using last/last_value with PARTITION BY/ORDER BY has unexpected results in Data Engineering

Window function using last/last_value with PARTITION BY/ORDER BY has unexpected results

alexisjohnson — Thu, 18 Nov 2021 18:46:17 GMT

Hi, I'm wondering if this is the expected behavior when using last or last_value in a window function? I've written a query like this:

select
    col1,
    col2,
    last_value(col2) over (partition by col1 order by col2) as column2_last
from values
    (1, 10), (1, 11), (1, 12),
    (2, 20), (2, 21), (2, 22);

In Snowflake I get the following results. The behavior feels right, since we're partitioning by col1 and ordering by col2 (https://docs.snowflake.com/en/sql-reference/functions/last_value.html).

In Databricks, I get:

It seems like the ORDER BY doesn't get applied... Perhaps I am writing the query incorrectly?

Any insight into this behavior would be very helpful. Also, I'm curious why last and last_value are synonyms? https://docs.databricks.com/sql/language-manual/functions/last.html

Thanks!

Re: Window function using last/last_value with PARTITION BY/ORDER BY has unexpected results

Hubert-Dudek — Thu, 18 Nov 2021 20:44:55 GMT

yes indeed it looks like a bug with LAST_VALUE,

Min gives correct results:

Max hmm only when desc is specified (shouldn't affect as max is max but only with descending sorting it works as should be):

for last_value is exactly like on your example (so nothing helps - I tried many ways).

@Kaniz Fatma maybe someone from inside databricks could look at it

Re: Window function using last/last_value with PARTITION BY/ORDER BY has unexpected results

alexisjohnson — Thu, 18 Nov 2021 20:47:44 GMT

Agreed. Interesting find that `max` behaves funny when `desc` isn't specified.

I'd also note that `first_value` yields the correct result.

@Kaniz Fatma Is there a process for filing bugs in Databricks? It'd be great to get this fixed.

Re: Window function using last/last_value with PARTITION BY/ORDER BY has unexpected results

alexisjohnson — Thu, 18 Nov 2021 20:48:28 GMT

Thank you for your help, @Hubert Dudek !

Re: Window function using last/last_value with PARTITION BY/ORDER BY has unexpected results

Anonymous — Fri, 19 Nov 2021 16:38:49 GMT

@Alexis Johnson - Here is where you can open a support ticket:

Re: Window function using last/last_value with PARTITION BY/ORDER BY has unexpected results

Carv — Wed, 12 Jul 2023 20:00:31 GMT

For those stumbling across this; it seems LAST_VALUE emulates the same functionality as it does in SQL Server which does not, in most people's minds, have a proper row/range frame for the window. You can adjust it with the below syntax.

I understand last_value emulating what is in my mind a mistake from sql_server but I don't understand why last() (an alias for last_value) has to use the same window row range.

SELECT col1 , col2 , LAST_VALUE(col2) OVER (PARTITION BY col1 ORDER BY col2 ASC) as column2_last , LAST_VALUE(col2) OVER (PARTITION BY col1 ORDER BY col2 ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as column2_last_unbound , FIRST_VALUE(col2) OVER (PARTITION BY col1 ORDER BY col2 ASC) as column2_first , FIRST_VALUE(col2) OVER (PARTITION BY col1 ORDER BY col2 DESC) as column2_first_reversed FROM values (1,10), (1,12), (1,11), (2,20), (2,21), (2,22);

Re: Window function using last/last_value with PARTITION BY/ORDER BY has unexpected results

asbivu — Sat, 03 Oct 2026 10:25:29 GMT

The behavior of LAST_VALUE with PARTITION BY and ORDER BY can be confusing when the window frame affects which row is considered the last value. I’ve found that checking the window frame definition carefully helps make these results easier to understand. For office window film in San Antonio, similar attention to detail can help when choosing solutions for large glass spaces.