Window Functions: last_value(ORDER BY … ASC) same as last_value(ORDER BY … DESC)

by

The problem with LAST_VALUE() is that the default rules for windowing clauses remove the values that you really want. This is a very subtle problem and is true in all databases that support this functionality.

This comes from an Oracle blog:

Whilst we are on the topic of windowing clauses, the implicit and
unchangeable window clause for the FIRST and LAST functions is ROWS
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, in other words
all rows in our partition. For FIRST_VALUE and LAST_VALUE the default
but changeable windowing clause is ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW, in other words we exclude rows after the current one.
Dropping rows off the bottom of a list makes no difference when we are
looking for the first row in the list (FIRST_VALUE) but it does make a
difference when we are looking for the last row in the list
(LAST_VALUE) so you will usually need either to specify ROWS BETWEEN
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING explicitly when using
LAST_VALUE or just use FIRST_VALUE and reverse the sort order.

Hence, just use FIRST_VALUE(). This does what you want:

with test (id, session_ID, value) as (
      (VALUES (0, 2, 100),
              (1, 2, 120),
              (2, 2, 140),
              (3, 1, 900),
              (4, 1, 800),
              (5, 1, 500)
      )
     )
select id,
       first_value(value) over (partition by session_ID order by id) as first_value_window,
       first_value(value) over (partition by session_ID order by id desc) as first_value_window_desc
from test
order by id

More Related Contents:

  1. PostgreSQL unnest() with element number
  2. Dynamic alternative to pivot with CASE and GROUP BY
  3. PostgreSQL: running count of rows for a query ‘by minute’
  4. Calculating Cumulative Sum in PostgreSQL
  5. GROUP BY and aggregate sequential numeric values
  6. PostgreSQL equivalent for TOP n WITH TIES: LIMIT “with ties”?
  7. Get the distinct sum of a joined table column
  8. Window Functions or Common Table Expressions: count previous rows within range
  9. Postgres window function and group by exception
  10. Referencing current row in FILTER clause of window function
  11. How to group timestamps into islands (based on arbitrary gap)?
  12. How to use a ring data structure in window functions
  13. Conditional lead/lag function PostgreSQL?
  14. Does PostgreSQL support “accent insensitive” collations?
  15. Split comma separated column data into additional columns
  16. IN vs ANY operator in PostgreSQL
  17. How to delete duplicate entries?
  18. Transpose latest rows per user to columns
  19. Split column into multiple rows in Postgres
  20. Compute percents from SUM() in the same SELECT sql query
  21. What is the equivalent PostgreSQL syntax to Oracle’s CONNECT BY … START WITH?
  22. How to find a table having a specific column in postgresql
  23. postgresql: INSERT INTO … (SELECT * …)
  24. PostgreSQL JOIN with array type with array elements order, how to implement?
  25. Trim trailing spaces with PostgreSQL
  26. Return multiple columns and rows from a function PostgreSQL instead of record
  27. Postgres error updating column data
  28. Check whether string is a date Postgresql
  29. PostgreSQL: Full Text Search – How to search partial words?
  30. PostgreSQL – dynamic value as table name [duplicate]

Leave a Comment