Get the Value from a previous record using Oracle

Michael
Michael used Ask the Experts™
on
I have select statement where I need to get the date (LOG.Date) from the previous row. Please help?

Thanks

Select
 ,LOG.ID
 ,LOG.Date
 ,LOG.Name
From
 LOGS
Where LOG.Date >= trunc(sysdate)-45
ORDER BY LOG.Date
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
What does it mean previous record.

If previous means first record with LOG.Date<trunc(sysdate)-45
then use:

SELECT * FROM
FROM
(
Select
 ,LOG.ID
 ,LOG.Date
 ,LOG.Name
From
 LOGS
Where LOG.Date<trunc(sysdate)-45
ORDER BY LOG.Date DESC
) subQ
WHERE ROWNUM = 1;

Author

Commented:
I'm sorry. The previous record would be either the first out of two or 99th out of 100. I need the value of the date column from that record to subtract to the value date column in the current record.

Thanks
Commented:
Hi,
You should the LEAD or LAG functions
http://www.oracle-base.com/articles/misc/LagLeadAnalyticFunctions.php

something like

SELECT empno,
       ename,
       job,
       sal,
       LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,
       sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_diff
FROM   emp;
Select
 ,LOG.ID
 ,LOG.Date
 LAG(LOG.Date, 1) OVER (ORDER BY LOG.Date) AS date_prev,
 ,LOG.Name
From
 LOGS
Where LOG.Date >= trunc(sysdate)-45
ORDER BY LOG.Date

Open in new window


Hope it helps
expert nicholas has given the exact solution, besides this you might also need to consider the situation where previous row is null, ie.,incase the offset in lag function is not defined it may return null which may result in exception of when no_data_found in query so to avoid it just add the offset to the above query which nicholas has said, like this(considering sysdate-45 is default date)

Select
 ,LOG.ID
 ,LOG.Date
 LAG(LOG.Date, 1,trunc(sysdate-45)) OVER (ORDER BY LOG.Date) AS date_prev,
 ,LOG.Name
From
 LOGS
Where LOG.Date >= trunc(sysdate)-45
ORDER BY LOG.Date

Author

Commented:
The second answer was spot on!

Thanks so much for your help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial