Make delicious recipes!

Lead/Lag analytic functions

Lead/Lag functions help to get data from the next/previous row in a query.

Syntax for these functions is:

Lead (expr [, offset, default-val ] ) over (order by expr)

Lag (expr [, offset, default-val ] ) over (order by expr)


Expr can be anything except another analytic function i.e. these functions cannot be nested.

Offset is the number of rows looked ahead or behind. Default is 1

Default-val is returned from these functions, if there is no row at the given offset from current row. Default is null.


Example:



The above functions can come very handy when we want to have a difference or sum or average etc between two consecutive rows in a query.






Like us on Facebook to remain in touch
with the latest in technology and tutorials!


Got a thought to share or found a
bug in the code?
We'd love to hear from you:

Name:
Email: (Your email is not shared with anybody)
Comment:

Facebook comments:

Site Owner: Sachin Goyal