title: Time between two non consecutive rows? (MySQL)
body: <p>I've looked here <a href="https://stackoverflow.com/questions/7937288/calculate-the-time-difference-between-of-two-rows/7937333#7937333">Calculate the time difference between of two rows</a>. </p>
  
  <p>I modified the code here:</p>
  
  <p><code>SELECT A.Test_ID, B.TestDate - A.TestDate AS DaysBetweenTests
  FROM Exams A
  JOIN Exams B ON B.Test_ID = A.Test_ID
  WHERE
  (SELECT B.Test_ID FROM B.Exams 
  ORDER BY Animal_ID, B.TestDate LIMIT 1 OFFSET 1)</code></p>
  
  <p>This query isn't complete. I'm trying to calculate the days between <strong>TestDate</strong> with the next <strong>TestDate</strong> for the same <code>Animal_ID</code>. The problem here is that an <code>Animal_ID</code> <strong>TestDate</strong> is not directly consecutive with <code>Test_ID</code>. I can't use <code>Test_ID +1</code>.</p>
  
  <p>Sample Exams table:</p>
  
  <pre><code>Test_ID  |  TestDate  |  Animal_ID
  1           2013-01-01   100
  2           2007-06-18   162
  3           2013-02-01   100
  4           2013-04-16   100
  </code></pre>
  
  <p>The <strong>TestDate</strong> for a single <code>Animal_ID</code> is always increasing in order, thus a higher <code>Test_ID number</code>. </p>
  
  <p>I'm having trouble with the subquery.</p>