title: MySQL calculating row differences with unstructured data
body: <p>I have a table messages with columns: phonenumber, type, timestamp.<br>
  Type indicates if the message is a question or an answer.</p>
  
  <p>Now I need a query that can calculate the response time between a question and an answer but the problem is that there exists no relationship between question &amp; answer except for the fact that they have the same phone number.</p>
  
  <p>So these 2 records belong together:  </p>
  
  <pre><code>Phonenumber | Type     | Timestamp  
    123456789 | Question | xxxxxx  
    123456789 | Answer   | xxxxxx
  </code></pre>
  
  <ul>
  <li><p>It's possible that there are multiple questions from the same phonenumber before an answer is send to that phonenumber. In that case the timestamp from the last message should be used: </p>
  
  <pre><code>Phonenumber | Type     | Timestamp  
    123456789 | Question | xxxxxx  
    123456789 | Question | xxxxxx  
    123456789 | Question | xxxxxx  &lt;-  
    123456789 | Answer   | xxxxxx
  </code></pre></li>
  <li><p>It is not Q -> A, Q -> A in the database, so something like this is possible:  </p>
  
  <pre><code>Phonenumber | Type     | Timestamp  
    123456789 | Question | xxxxxx  
    987654321 | Question | xxxxxx  
    987654321 | Answer   | xxxxxx  
    123456789 | Answer   | xxxxxx
  </code></pre></li>
  <li><p>1 Phonenumber can have multiple sets of Q -> A. All these sets should be in the calculation:  </p>
  
  <pre><code>Phonenumber | Type     | Timestamp  
    123456789 | Question | xxxxxx  
    123456789 | Answer   | xxxxxx  
    123456789 | Question | xxxxxx  
    123456789 | Answer   | xxxxxx  
  </code></pre></li>
  </ul>
  
  <p>To be honest I have no idea how to even start on this. I have found some things about calculating the difference between 2 rows but than I will need to have my data completely structured first. (Q -> A, Q -> A, Q -> A, ...).  </p>
  
  <p>I know it would be much easier if there would be a relationship defined between a Q and the corresponding A but that's not available :/</p>
  
  <p>SQL Fiddle link unstructured data: <a href="http://sqlfiddle.com/#!2/4dff3/1/0" rel="nofollow">http://sqlfiddle.com/#!2/4dff3/1/0</a><br>
  SQL Fiddle link structured data: <a href="http://sqlfiddle.com/#!2/61a62/1/0" rel="nofollow">http://sqlfiddle.com/#!2/61a62/1/0</a><br>
  Once I have this I can calculate the average response time by calculating the timestamp different between row2-row1, row4-row3, row6-row5.</p>
  
  <p>UPDATE:<br>
  <code>SELECT t1.answer AS answer, t1.timeOfAnswer AS timeOfAnswer, MIN(t1.responseTime) AS responseTime 
      FROM (
      SELECT
        a.message AS answer,
        a.timestamp AS timeOfAnswer,
        timestampdiff(second, q.timestamp, a.timestamp) AS responseTime 
      FROM message q 
      JOIN message a ON q.phoneNumber = a.phoneNumber 
      WHERE q.type = 0 AND a.type = 1
      HAVING responseTime &gt; 0
    ) AS t1
    GROUP BY t1.answer</code><br>
  With this query it works fine on my sample data because the message content is named like 'Message 1 answer', 'Message 2 answer', ... So I can do GROUP BY on that field. But on the real data it's possible that the same answer gets send multiple times.</p>