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

asked 02 Feb '17, 03:39

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%


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

permanent link

answered 02 Feb '17, 03:49

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 02 Feb '17, 03:59

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×19
×6

question asked: 02 Feb '17, 03:39

question was seen: 4,628 times

last updated: 02 Feb '17, 03:59