# BigQuery Tips: Why LAST_VALUE keeps returning the wrong result

> Say you want to find the entry page and exit page for every session in your events table. The natural query writes itself: SELECT CONCAT(ga_session_id, '-', user_pseudo_id) AS session_key, event_timestamp, page_path, FIRST_VALUE(page_path) OVER ( PARTITION BY CONCAT(ga_session_id, '-', user_pseudo_id) ORDER BY event_timestamp ) AS entry_page, LAST_VALUE(page_path) OVER ( PARTITION BY CONCAT(ga_session_id, '-', user_pseudo_id) ORDER BY event_timestamp ) AS exit_pag

Source: https://www.measurelab.co.uk/insights/blog/bigquery-tips-why-last-_value-keeps-returning-the-wrong-result/  
Author: Katie Kaczmarek  
Published: 2026-07-13  
Updated: 2026-10-13

Say you want to find the entry page and exit page for every session in your events table. The natural query writes itself:

```
SELECT
  CONCAT(ga_session_id, '-', user_pseudo_id) AS session_key,
  event_timestamp,
  page_path,
  FIRST_VALUE(page_path) OVER (
    PARTITION BY CONCAT(ga_session_id, '-', user_pseudo_id)
    ORDER BY event_timestamp
  ) AS entry_page,
  LAST_VALUE(page_path) OVER (
    PARTITION BY CONCAT(ga_session_id, '-', user_pseudo_id)
    ORDER BY event_timestamp
  ) AS exit_page
FROM base
```

Run this and `entry_page` works perfectly. `exit_page` returns the wrong result for every row except the last one in the session. No error, no warning, just quietly incorrect data.

### **What is actually happening**

Both functions use a window defined by `PARTITION BY` and `ORDER BY`. What most people do not realise is that `ORDER BY` also sets a default frame for what those functions consider their window.

The default frame is: **from the start of the partition to the current row**.

For `FIRST_VALUE` this does not matter. The first row of the window is always the start of the partition, regardless of how large the frame is. It returns the right value every time.

For `LAST_VALUE` it matters a lot. With the default frame, the last row of the window is the current row — not the end of the partition. So `LAST_VALUE` returns the value on the row you are currently on, which is the same as just writing `page_path`. It is useless, and it looks correct until you check the numbers.

### **Fix one: extend the frame**

Tell `LAST_VALUE` to look all the way to the end of the partition:

```
LAST_VALUE(page_path) OVER (
  PARTITION BY CONCAT(ga_session_id, '-', user_pseudo_id)
  ORDER BY event_timestamp
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS exit_page
```

Now the frame runs from the first row to the last row of the partition, and `LAST_VALUE` correctly returns the final value.

This works, but `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` is long and easy to get wrong. There is a cleaner option.

### **Fix two: reverse the order and use FIRST_VALUE**

`FIRST_VALUE` always works correctly with the default frame. So instead of fighting `LAST_VALUE`, reverse the sort order and use `FIRST_VALUE`:

```
SELECT
  CONCAT(ga_session_id, '-', user_pseudo_id) AS session_key,
  event_timestamp,
  page_path,
  FIRST_VALUE(page_path) OVER (
    PARTITION BY CONCAT(ga_session_id, '-', user_pseudo_id)
    ORDER BY event_timestamp ASC
  ) AS entry_page,
  FIRST_VALUE(page_path) OVER (
    PARTITION BY CONCAT(ga_session_id, '-', user_pseudo_id)
    ORDER BY event_timestamp DESC
  ) AS exit_page
FROM base
```

`ORDER BY event_timestamp DESC` puts the last event at the top of the window. `FIRST_VALUE` picks it up correctly. No frame clause needed, and the intent is immediately readable.

This is the version most people land on once they have understood the problem.

### **The thing worth remembering**

`FIRST_VALUE` and `LAST_VALUE` are not symmetric. `FIRST_VALUE` works correctly with the default window frame. `LAST_VALUE` does not. The asymmetry is not a bug, it is how window frames are defined in SQL, but it is not something most people are taught explicitly, which is why it keeps catching people out.

Any time you reach for `LAST_VALUE`, either write the full frame clause or ask yourself whether reversing the sort and using `FIRST_VALUE` gets you there more cleanly. The choice is yours.
