Collections:
LAG() - N-Row before Current Row within Window
How to evaluate a field expression on n-row before the current row in the current result set window using the LAG() function?
✍: FYIcenter.com
LAG(exp, n, default) is a MySQL built-in window function that evaluates a field expression on n-row before the current row in the current result set window. For example:
SELECT help_topic_id AS tip, help_category_id AS cid, name, LAG(name, 2, NULL) OVER w FROM mysql.help_topic WINDOW w AS (PARTITION BY help_category_id); -- +-----+-----+----------------+----------------------------+ -- | tip | cid | name | LAG(name, 2, NULL) OVER w | -- +-----+-----+----------------+----------------------------+ -- | 0 | 1 | HELP_DATE | NULL | -- | 1 | 1 | HELP_VERSION | NULL | -- | 2 | 2 | AUTO_INCREMENT | NULL | -- | 6 | 2 | BIT | NULL | -- | 7 | 2 | TINYINT | AUTO_INCREMENT | -- | 8 | 2 | BOOLEAN | BIT | -- | 9 | 2 | SMALLINT | TINYINT | -- | 10 | 2 | MEDIUMINT | BOOLEAN | -- ... -- +-----+-----+----------------+----------------------------+
Reference information of the LAG() function:
LAG(exp, n, default): val Evaluates a field expression on n-row before the current row in the current result set window. Arguments, return value and availability: exp: Required. The field expression to be evaluated. n: Optional. Default is 1. The number of rows lagging the current row. default: Optional. Default is NULL. The default value for non-existing rows. val: Return value. The exp value on n-row before the current row. Available since MySQL 8.
Related MySQL functions:
⇒ LAST_VALUE() - Last Value of Result Set Window
⇐ FIRST_VALUE() - First Value of Result Set Window
2023-12-19, 270🔥, 0💬
Popular Posts:
How To Locate and Take Substrings with CHARINDEX() and SUBSTRING() Functions in SQL Server Transact-...
How To Format Time Zone in +/-hh:mm Format in SQL Server Transact-SQL? From the previous tutorial, y...
How to execute statements under given conditions in SQL Server Transact-SQL? How to use IF ... ELSE ...
How To Divide Query Output into Multiple Groups with the GROUP BY Clause in SQL Server? Sometimes, y...
How REAL and FLOAT Literal Values Are Rounded in SQL Server Transact-SQL? By definition, FLOAT(n) sh...