Link Search Menu Expand Document

LAG

Returns the value of the input expression at the given offset before the current row within the requested window.

For more information on usage, please refer to Window Functions.

Syntax

LAG ( <expression> [, <offset> [, <default> ]] )
    OVER ( [ PARTITION BY <partition_by> ] ORDER BY <order_by> [ { ASC | DESC } ] )

Parameters

Parameter Description Supported input types
<value> Any valid expression that will be returned based on the <offset>. Any
<partition_by> The expression used for the PARTITION BY clause. Any
<offset> The number of rows backward from the current row from which to obtain a value. A negative number will act as LEAD() INTEGER
<default> The expression to return when the offset goes out of the bounds of the window. Must be a literal INTEGER. The default is NULL. INTEGER
<order_by> An expression used for the order by clause. Any

Example

In the example below, the LAG function is being used to find the players in each level who ranked above and below a certain player. In some cases, if the player has no one ranked above or below them, the LAG function returns NULL.

SELECT
	nickname,
	level,
	LAG(nickname, 1) OVER (PARTITION BY level ORDER BY nickname ) AS rank_above,
	LAG(first_name, -1) OVER (PARTITION BY level ORDER BY first_name ) AS rank below
FROM
	players;

Returns:

nickname level rank_above rank_below
kennethpark 9 NULL rileyjon
rileyjon 9 kennethpark sabrina21
sabrina21 9 rileyjon ymatthews
ymatthews 9 sabrina21 NULL