How to simulate the LAG/LEAD window functions?

[VolkerBarth]

One of the few features I'll miss in SQL Anywhere compared to MS SQL Server are the LAG and LEAD window functions:

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...

[VolkerBarth · Accepted solution]

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...)