?
Solved

Get the Value from a previous record using Oracle

Posted on 2012-03-27
5
Medium Priority
?
555 Views
Last Modified: 2012-03-28
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
0
Comment
Question by:MIREESE
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 3

Expert Comment

by:zofcentr
ID: 37775114
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;
0
 

Author Comment

by:MIREESE
ID: 37775159
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
0
 
LVL 6

Accepted Solution

by:
NikolasG earned 2000 total points
ID: 37775202
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
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37775831
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
0
 

Author Closing Comment

by:MIREESE
ID: 37777160
The second answer was spot on!

Thanks so much for your help!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question