title: Using LAG to Find Previous Value in Oracle
body: <p>I'm trying to use the LAG function in Oracle to find the previous registration value for donors.</p>
  
  <p>To see my data, I started with this query to find all registrations for the particular donor:</p>
  
  <p><code>select registration_id, registration_date from registration r where r.person_id=52503290 order by r.registration_date desc;</code></p>
  
  <p><img src="https://i.stack.imgur.com/zcNpU.jpg" alt="Registrations"></p>
  
  <p>Then I used the <code>LAG</code> function to return the previous value along with the most recent value:</p>
  
  <p><code>select registration_id as reg_id, registration_date as reg_date,
  lag(registration_date,1) over (order by registration_date) as prev_reg_date
  from registration
  where person_id=52503290
  order by registration_date desc;</code></p>
  
  <p>And the results are as expected:</p>
  
  <p><img src="https://i.stack.imgur.com/F1X7E.jpg" alt="LAG Function"></p>
  
  <p>So I thought I should be good to place the <code>LAG</code> function within the main query to get the previous value but for some reason, the previous value returns NULL or no value at all.</p>
  
  <p><code>SELECT
         P.Person_Id AS Person_ID,
         R.Registration_Date AS Drive_Date,
         LAG(R.Registration_Date,1) OVER (ORDER BY R.REGISTRATION_DATE) AS Previous_Drive_Date,
         P.Abo AS Blood_Type,
         DT.Description AS Donation_Type
  FROM
         Person P
         JOIN Registration R ON P.Person_Id = R.Person_Id AND P.First_Name &lt;&gt; 'Pooled' AND P.First_Name &lt;&gt; 'IMPORT'
         LEFT OUTER JOIN Drives DR ON R.Drive_Id = DR.Drive_Id AND DR.Group_Id &lt;&gt; 24999
         LEFT OUTER JOIN Branches B ON R.Branch_Id = B.Branch_Id
         LEFT OUTER JOIN Donor_Group DG on DR.Group_Id = DG.Group_Id
         LEFT OUTER JOIN Donation_Type DT ON R.Donation_Type_Id = DT.DONATION_TYPE_ID
  WHERE
         TRUNC(R.Registration_Date) = TRUNC(SYSDATE)-1
         AND R.Person_Id=52503290
  ORDER BY
        R.Registration_Date DESC;</code></p>
  
  <p>Here is the result set:</p>
  
  <p><img src="https://i.stack.imgur.com/9i3CS.jpg" alt="Main Query"></p>
  
  <p>Any suggestions on what I am missing here? Or why this query isn't returning the values expected?</p>
  
  <p>Based on @Alex Poole's suggestions, I changed the query to look like:</p>
  
  <pre><code>SELECT * FROM (
  SELECT
         P.Person_Id AS Person_ID,
         R.Registration_Date AS Drive_Date,
         LAG(R.Registration_Date,1) OVER (partition by p.person_id ORDER BY r.registration_date) AS Previous_Drive_Date,
         P.Abo AS Blood_Type,
         DT.Description AS Donation_Type
  FROM
         Person P
         JOIN Registration R ON P.Person_Id = R.Person_Id AND P.First_Name &lt;&gt; 'Pooled' AND P.First_Name &lt;&gt; 'IMPORT'
         LEFT OUTER JOIN Drives DR ON R.Drive_Id = DR.Drive_Id AND DR.Group_Id &lt;&gt; 24999
         LEFT OUTER JOIN Branches B ON R.Branch_Id = B.Branch_Id
         LEFT OUTER JOIN Donor_Group DG on DR.Group_Id = DG.Group_Id
         LEFT OUTER JOIN Donation_Type DT ON R.Donation_Type_Id = DT.DONATION_TYPE_ID
  --WHERE R.Person_Id=52503290
  )
  WHERE TRUNC(Drive_Date) = TRUNC(SYSDATE)-1
  ORDER BY Drive_Date DESC;
  </code></pre>
  
  <p>It takes about 85 seconds to pull back the first 30 rows:</p>
  
  <p><img src="https://i.stack.imgur.com/ndtgt.jpg" alt="Query Results"></p>
  
  <p>My original query (before the <code>LAG</code> function was added) took about 2 seconds to pull back approximately 2100 records. But it was nothing but a SELECT with a couple of JOINS and one item in the WHERE clause.</p>
  
  <p>Looking at the record counts, Person has almost 5.5 million records and Registration has 9.1 million records.</p>