title: Oracle find previous record if there is one
body: <p>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.</p>
  
  <pre><code>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 &gt;= 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 &lt; t1.date)
    and t3.date = (select max(t3a.date) from table t3a
                   where t3a.index = t1.index
                     and t3a.date &lt; t2.date)
  </code></pre>
  
  <p>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.  </p>
  
  <p>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.)  </p>
  
  <p>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"?</p>