> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# LAG

> Returns a value from an earlier row in the same group defined by PARTITION BY.

<div className="e6-meta"><span className="e6-eyebrow e6-eyebrow--quiet">Window function</span></div>

NULL values count toward the offset and can be returned. If the offset goes beyond the group, the default is returned; the default is NULL when omitted. IGNORE NULLS skips NULL values when locating the requested row.

## Syntax

A window function keeps each input row and adds a result for that row. `PARTITION BY` groups rows with matching values; without it, all input rows form one group.

### Window

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
lag(value [, offset [, default]]) [IGNORE NULLS | RESPECT NULLS] OVER ([PARTITION BY key] ORDER BY ordering_key)
```

## Arguments

| Name | Type | Required | Description |
| - | - | - | - |
| value | BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(p, s), STRING, BINARY, DATE, TIME, TIMESTAMP, ARRAY, MAP or STRUCT | Yes | Expression from the selected row. For DECIMAL(p, s), p is precision (total digits) and s is scale (digits after the decimal point). |
| offset | INTEGER or BIGINT | No | Number of rows to move. Must be a constant argument. Default: 1. |
| default | same type as value after type conversion | No | Value used when the offset is outside the group of rows. Must be a constant argument. Default: NULL. |

## Returns

* Type: same SQL type as value after type conversion with default
* NULL behavior: NULL values count toward the offset and can be returned. If the offset goes beyond the group, the default is returned; the default is NULL when omitted. IGNORE NULLS skips NULL values when locating the requested row.
* Empty input: An empty input produces no rows.

## Properties

| Property | Value |
| - | - |
| Deterministic | Partial (Use an ORDER BY key that uniquely orders rows for stable offsets.) |
| Supports window usage | Yes |
| Supports window frames | No |
| Exact result | Yes |

## Usage notes

* Offset and default must be constant expressions. They cannot vary by row.
* Negative offsets reverse the direction; zero selects the current row.
* A NULL offset behaves like 0 and selects the current row. Omitting the offset uses 1.
* INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
* Query tools may report a DECIMAL result as DOUBLE or a TIME result as INTEGER. The returned value still keeps its decimal precision and scale, or its time type. A reported type does not convert the value.
* Explicit window-frame clauses are not supported for this function.

## Examples

### Default offset and value

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH t("value") AS (VALUES (1), (2), (3)) SELECT "value", lag("value") OVER (ORDER BY "value") AS result FROM t ORDER BY "value";
```

| value | result |
| - | - |
| 1 | NULL |
| 2 | 1 |
| 3 | 2 |

### Choose an offset and a default

The default -1 is used when there is no row two positions away.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH sample(id, v) AS (VALUES (1, 10), (2, 20), (3, 30))
SELECT id, v, lag(v, 2, -1) OVER (ORDER BY id) AS result
FROM sample ORDER BY id;
```

| id | v | result |
| - | - | - |
| 1 | 10 | -1 |
| 2 | 20 | -1 |
| 3 | 30 | 10 |

## Limitations

* Explicit window frame clauses are not supported.
* Use an integer offset. Fractional offsets are not supported.
* The default value cannot depend on a row’s contents.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.