Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
SQL Server 119
#SQLServer 88
Oracle 70
#SQL SERVER 35
BRH 31
SQL Server 2012 29
denali 23
#TSQL 19
TSQL 19
C# 15

Archive · View All
October 2011 31
November 2011 30
September 2011 30
August 2011 18
December 2011 15
July 2011 13
June 2011 8
May 2012 4
April 2012 3
January 2010 3

Day 36: Lag Function of Oracle

Oct 19 2011 10:53PM by Niladri Biswas   

Let Us Learn Oracle - Part 36 of N [ Lag function of Oracle ]

Purpose:It is an analytic function.This function returns result set starting from the previous row in the table. This function is available since Oracle 8i.

Syntax:Lag(expression [,offset [,default] ] ) over( [ Partition_By_clause] order by clause)

Where,

Expression => A table column or built-in function but not analytical functions

Offset => It is optional and represents the physical offset from the current row in the table. If not specified, the default value is 1 and cannot accept negative numbers.

Default = > It is again optional. If not specified, then whenever the offset value goes out of the table bounds, then default null is returned.

Partition_By_clause = > Partition the query result set. It is again optional

Order By Clause = > Indicates how the data is ordered within the partition.

N.B.~Lag and Lead are the opposite side of a coin and henceforth whatever example we have seen for Lead will be applicable for Lag. Henceforth, it will not be wise to repeat the same scenarios here.We will see some of the example of Lag though

Find the previous match date and the previous match between the teams

SQL> Select 
  2   MatchID
  3   ,MatchGroup
  4   ,MatchBetween
  5   ,ScheduleDate
  6   ,Lag (MatchBetween) Over(Order by ScheduleDate) PrevMatchBetween
  7   ,Lag (ScheduleDate) Over(Order by ScheduleDate) PrevMatchDate
  8  From MatchTable; 

   MATCHID MATCHGROUP MATCHBETWEEN                                       SCHEDULED PREVMATCHBETWEEN                                   PREVMATCH
---------- ---------- -------------------------------------------------- --------- -----------------
         1 Group-A    India VS Australia                                 14-AUG-11
         2 Group-A    India VS Pakistan                                  15-AUG-11 India VS Australia                                 14-AUG-11
         3 Group-A    India VS Newzealand                                16-AUG-11 India VS Pakistan                                  15-AUG-11
         4 Group-A    Australia VS Pakistan                              17-AUG-11 India VS Newzealand                                16-AUG-11
         5 Group-A    Australia VS Newzealand                            18-AUG-11 Australia VS Pakistan                              17-AUG-11
         6 Group-A    Newzealand VS Pakistan                             19-AUG-11 Australia VS Newzealand                            18-AUG-11
         7 Group-B    USA VS WestIndies                                  20-AUG-11 Newzealand VS Pakistan                             19-AUG-11
         8 Group-B    USA VS Ireland                                     21-AUG-11 USA VS WestIndies                                  20-AUG-11
         9 Group-B    USA VS Bangaladesh                                 22-AUG-11 USA VS Ireland                                     21-AUG-11
        10 Group-B    WestIndies VS Ireland                              23-AUG-11 USA VS Bangaladesh                                 22-AUG-11
        11 Group-B    WestIndies VS Bangaladesh                          24-AUG-11 WestIndies VS Ireland                              23-AUG-11
        12 Group-B    Ireland VS Bangaladesh                             25-AUG-11 WestIndies VS Bangaladesh                          24-AUG-11

12 rows selected.

The above result can also be achieve by using the below query

SQL> Select 
  2   MatchID
  3   ,MatchGroup
  4   ,MatchBetween
  5   ,ScheduleDate
  6   ,Lag (MatchBetween,1) Over(Order by ScheduleDate) PrevMatchBetween
  7   ,Lag (ScheduleDate,1) Over(Order by ScheduleDate) PrevMatchDate
  8  From MatchTable; 

   MATCHID MATCHGROUP MATCHBETWEEN                                       SCHEDULED PREVMATCHBETWEEN                                   PREVMATCH
---------- ---------- -------------------------------------------------- --------- -----------------
         1 Group-A    India VS Australia                                 14-AUG-11
         2 Group-A    India VS Pakistan                                  15-AUG-11 India VS Australia                                 14-AUG-11
         3 Group-A    India VS Newzealand                                16-AUG-11 India VS Pakistan                                  15-AUG-11
         4 Group-A    Australia VS Pakistan                              17-AUG-11 India VS Newzealand                                16-AUG-11
         5 Group-A    Australia VS Newzealand                            18-AUG-11 Australia VS Pakistan                              17-AUG-11
         6 Group-A    Newzealand VS Pakistan                             19-AUG-11 Australia VS Newzealand                            18-AUG-11
         7 Group-B    USA VS WestIndies                                  20-AUG-11 Newzealand VS Pakistan                             19-AUG-11
         8 Group-B    USA VS Ireland                                     21-AUG-11 USA VS WestIndies                                  20-AUG-11
         9 Group-B    USA VS Bangaladesh                                 22-AUG-11 USA VS Ireland                                     21-AUG-11
        10 Group-B    WestIndies VS Ireland                              23-AUG-11 USA VS Bangaladesh                                 22-AUG-11
        11 Group-B    WestIndies VS Bangaladesh                          24-AUG-11 WestIndies VS Ireland                              23-AUG-11
        12 Group-B    Ireland VS Bangaladesh                             25-AUG-11 WestIndies VS Bangaladesh                          24-AUG-11

12 rows selected.

Now let us try to understand the behavior of the Lag function. We have not specified any offset in this query and hence the default value of 1 has been taken into granted. Henceforth, it ended at N-1 th row from the bottom. However, if we explicitly specify the offset value as 1, it will return the same result.

As specified earlier, whatever example follows for Lead will be applicable for Lag. Henceforth, the scenarios are not repeated here

Prior to Denali CTP3, there was no inbuilt Lag function.But we have this now.You can read about Lag of Sql Server from here

Hope you have enjoyed it.Thanks for reading

Tags: #SQLServer, SQL Server, Oracle,


Niladri Biswas
7 · 21% · 6720
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"Day 36: Lag Function of Oracle" rated 5 out of 5 by 1 readers
Day 36: Lag Function of Oracle , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]