Press "Enter" to skip to content

Accessing Values from Subsequent or Preceding Rows

Accessing Values from Subsequent or Preceding Rows

Problem

You would like to query data to produce an ordered result, but you want to include calculations based on
preceding and following rows in the result set. For instance, you want to perform calculations on eventstyle
data based on events that occurred earlier and later in time.

Solution

Oracle supports the

LAG and LEAD analytical functions to provide access to multiple rows in a table or

expression, utilizing preceding/following logic—and you won’t need to resort to joining the source data

to itself. Our recipe assumes you are trying to tackle the business problem of visualizing the trend in
hiring of staff over time. The

LAG function can be used to see which employee’s hiring followed another,

and also to calculate the elapsed time between hiring.

select first_name, last_name, hire_date,
lag(hire_date, 1, ’01-JUN-1987′) over (order by hire_date) as Prev_Hire_Date,
hire_date – lag(hire_date, 1, ’01-JUN-1987′) over (order by hire_date)
as Days_Between_Hires
from hr.employees
order by hire_date;

Our query returns 107 rows, linking the employees in the order they were hired (though not
necessarily preserving the implicit sort for display or other purposes), and showing the time delta
between each joining the organization.

FIRST_NAME LAST_NAME HIRE_DATE PREV_HIRE DAYS_BETWEEN

———– ———- ——— ——— ————
Steven King 17-JUN-87 01-JUN-87 16
Jennifer Whalen 17-SEP-87 17-JUN-87 92
Neena Kochhar 21-SEP-89 17-SEP-87 735
Alexander Hunold 03-JAN-90 21-SEP-89 104

Bruce Ernst 21-MAY-91 03-JAN-90 503
David Lee 23-FEB-00 06-FEB-00 17
Steven Markle 08-MAR-00 23-FEB-00 14
Sundar Ande 24-MAR-00 08-MAR-00 16
Amit Banda 21-APR-00 24-MAR-00 28
Sundita Kumar 21-APR-00 21-APR-00 0
107 rows selected.

You can calculate for yourself the day differences to confirm the

LAG function and difference

arithmetic are indeed working as claimed. For instance, there really are 503 days between January 3,

1990 and May 21, 1991.

Vinish Kapoor

I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me, it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub and get notifications for new posts.

You may also like:

Be First to Comment

    Leave a Reply

    Your email address will not be published. Required fields are marked *