LAG() in SQL
Accesses data from a previous row in the result set within a specified window partition.
Syntax
Return type
LAG() Function Example
What is LAG() in SQL?
The LAG() function in SQL allows you to access data from a previous row within the same result set, based on a specified window partition and ordering. It is commonly used for trend analysis, comparing current and previous values, calculating differences, and detecting changes over time.
This function is available in SQL Server, PostgreSQL, Oracle, and MySQL 8.0+ as part of window functions.
This function is widely used in time-series analysis, financial reports, and performance tracking.
Parameters:
expression: Column or expression to retrieve from previous row
offset (optional): Number of rows back to look (default: 1)
default_value (optional): Value to return when no previous row exists
Example Use Cases:
-- Calculate month-over-month growth
-- Compare with previous year by category
-- Track price changes
Notes:
Behavior: Returns NULL if no previous row and no default specified
Performance Considerations: Window function optimization may be needed
Version Info: Core window function available in most modern DBMS
Deprecated/Recommended Alternatives: None, standard functionality
Error Handling:
Error: Syntax error if ORDER BY is omitted
Recommendation: Always include ORDER BY clause and consider default values
Supported Databases:
DBMS
Function / Syntax
Example
Behavior with NULL
MySQL
LAG()
LAG(col) OVER (ORDER BY date)
Returns NULL
PostgreSQL
LAG()
LAG(col) OVER (ORDER BY date)
Returns NULL
SQL Server
LAG()
LAG(col) OVER (ORDER BY date)
Returns NULL
SQLite
LAG()
LAG(col) OVER (ORDER BY date)
Returns NULL
BigQuery
LAG()
LAG(col) OVER (ORDER BY date)
Returns NULL
Snowflake
LAG()
LAG(col) OVER (ORDER BY date)
Returns NULL
Athena
LAG()
LAG(col) OVER (ORDER BY date)
Returns NULL
Related Functions:
LEAD()
Accesses data from a subsequent row in the result set within a specified window partition.
FIRST_VALUE()
Returns the first value in an ordered partition of a result set.
LAST_VALUE()
Returns the last value in an ordered partition of a result set.
ROW_NUMBER()
Assigns a unique sequential integer to rows within a result set partition.
