Oracle find previous record if there is one

Question

I have a query that gets the two previous records, based on some qualifications. That works fine, but it's not finding items if there are not at least three records. So, I need to modify my query below, but I'm not quite sure how.

select t1.index
     , t1.date
     , t1.flag
     , t2.date
     , t2.flag
     , t3.date
     , t3.flag
from table t1
left outer join table t2
  on t2.index = t1.index
left outer join table t3
  on t3.index = t1.index
where t1.flag = '30'
  and t1.date >= to_date('05/08/2013','MM/DD/YYYY')
  and t2.date = (select max(t2a.date) from table t2a
                 where t2a.index = t1.index
                   and t2a.date < t1.date)
  and t3.date = (select max(t3a.date) from table t3a
                 where t3a.index = t1.index
                   and t3a.date < t2.date)

So, as long as there are at least three records with the same index field, it finds the most recent record (t1), then finds the next most recent record (t2), and then the one after that (t3), ordering by date.

I was working with lag functions and was not getting anything reliable, based on my complex linking and ordering (this example is dumbed down, because the index is in one table, the dates in an additional one linked through a third table.)

Essentially, I want the where statements to be "find the max date that matches the criteria that's less than what we already found, or if you didn't find anything more, then that's ok and return what you did find." How do I code the "or if you didn't find anything more"?

Answer 1

You might want to into the LEAD and LAG analytic functions. They work a little bit differently than what was stated in your question, returning (for LEAD) the 'next' and 'next+1' values as part of the same row instead of as separate rows, but perhaps they would prove useful. Example fiddle here.

Share and enjoy.

Answer 2

The problem here is that you are using outer joins to join to t2 and t3 but then putting conditions in the WHERE clause. If you move these conditions in to the JOIN clause, this should solve the problem.

As an alternative, you could try this query using an analytic function to remove the MAX functions from the query. This simplifies the logic a little and might make it perform better too.

with my_table as (
  select 
    index, 
    date, 
    flag,
    row_number() over (partition by index order by date desc) as rown
  from table
)
select t1.index
     , t1.date
     , t1.flag
     , t2.date
     , t2.flag
     , t3.date
     , t3.flag
from table t1
left outer join table t2
  on (t2.index = t1.index and t2.rown = t1.rown + 1)
left outer join table t2
  on (t3.index = t2.index and t3.rown = t2.rown + 1)
where t1.flag = '30'
  and t1.date >= to_date('05/08/2013','MM/DD/YYYY')

Answer 3

This is one way

select t1.index
     , t1.date
     , t1.flag
     , t2.date
     , t2.flag
     , t3.date
     , t3.flag
from table t1
left outer join table t2
  on t2.index = t1.index
  and t2.date = (select max(t2a.date) from table t2a
                 where t2a.index = t1.index
                   and t2a.date < t1.date)
left outer join table t3
  on t3.index = t1.index
  and t3.date = (select max(t3a.date) from table t3a
                 where t3a.index = t1.index
                   and t3a.date < t2.date)
where t1.flag = '30'
  and t1.date >= to_date('05/08/2013','MM/DD/YYYY')

Another wiould be to wrap your and clauses on T2 and T3 and use OR t2.date is null on the t2 link and T3.date is null on t3

as to why: the left joins return records from T2 and t3 only when they exist in T1. Which is not going to find a max on thus it's returning "Null" by evaluating for null on the join or in teh where clause it should work. This does however assume that your "date" field is always populated when a record exists.