LAG (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause ) LEAD (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause )
Here's a sample query from the MS SQL documentation:
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota, LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota FROM Sales.SalesPersonQuotaHistory WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');
Basically, LAG provides access to the value of the nth previous row in a result set, while LEAD provides access to that of the nth following row. By default, the offset is 1, so meaning the previous vs. next row, and a default can be specified if there is no such row or the scalar_expression would return NULL.
Q: So how do I simulate them with SQL Anywhere?
I'll try to propose a suggestion myself but please let me know whether there are better/easier supplements...
asked 02 Feb, 03:39
I guess I can simulare them with the help of the FIRST_VALUE aggregate function and an according one-row window:
LAG: ISNULL( FIRST_VALUE(scalar_expression RESPECT NULLS) OVER ([ partition_by_clause ] order_by_clause ROWS BETWEEN offset PRECEDING AND offset PRECEDING), default) LEAD: ISNULL( FIRST_VALUE(scalar_expression RESPECT NULLS) OVER ([ partition_by_clause ] order_by_clause ROWS BETWEEN offset FOLLOWING AND offset FOLLOWING), default)
Note: Words in lower case like "default" are just meant as arguments corresponding to the MS SQL syntax, not to be taken as the SQL Anywhere reserved words...
(For reasons of symmetry, LEAD may use LAST_VALUE() instead of FIRST_VALUE() as it is "looking forward", but as there is just one row, it does not make a difference...)