Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Equivalente of LAG/LEAD for SqlServer2005/2008

By Federico Iori,

SQLSever 2012 introduces SQL Analytic Functions LAG and LEAD in Oracle Style .

In SqlServer 2008 or earlier,  is possible, anyway, to obtain similar results using the TSQL APPLY operator .

Note that APPLY operator used in complex queries with large result can give lieu to poor performance, because APPLY executes using nested loop join algorythm : if needed, it will be necessary to ensure that first are executed hash joins and filter, and last  outer apply , only on the final query result set .

Total article views: 779 | Views in the last 30 days: 1
 
Related Articles
FORUM

Cross Apply

Cross Apply is Slow

BLOG

Cross Apply Ambiguity

Cross apply (and outer apply)  are a very welcome addition to the TSQL language.  However, today aft...

FORUM

CROSS Apply or Outer Apply? DDL attached

Return dynamic columns with CROSS APPLY

FORUM

to apply different logic for each ID and as per the logic and a single row from each ID(group) would be the result set

I have to apply different logic for each ID and as per the logic and a single row from each ID(group...

ARTICLE

Correlated Joins Using "Apply"

Discusses the use of the newly introduced APPLY Statement in SQL Server 2005

Tags
analytic functions    
apply    
lag    
lead    
 
Contribute