How to determine the difference among dates in the same column?

Question

How to determine the difference among dates of birth?

1. +----------+----------+------------+
2. | id | name | birth |
3. +----------+----------+------------+
4. | 00001 | Claws | 2010-04-17 |
5. | 00002 | Claws | 2010-01-31 |
6. | 00003 | Claws | 2009-11-31 |
7. | 00004 | Claws | 2009-09-31 |
8. | 00005 | Claws | 2009-07-31 |
9. | 00006 | Claws | 2008-10-31 |
10. +----------+----------+------------+

I would like to obtain this:

+----------+----------+------------+------------------------------------------+

1. | id | name | birth | diff |
2. +----------+----------+------------+------------------------------------------+
3. | 00001 | Claws | 2010-04-17 | diff (id1-id2) = 2010-01-31 - 2010-04-17 |
4. | 00002 | Claws | 2010-01-31 | diff (id2-id3) = 2010-01-31 - 2009-11-31 |
5. | 00003 | Claws | 2009-11-31 | diff (id3-id4) = 2010-01-31 - 2009-09-31 |

If possible, ORDER BY diff DESC

Thank you for helping

Vera

Answer 1

It looks like you'll want to use the datediff function, which returns the difference between two dates in days. Apply the abs function, if you want the difference to always be positive.

Also, it looks like you want to join the table to itself to get the row related to the current one (relation defined as related_row.id = row.id+1).

Without further knowledge of what the table definition is, or exactly how you want the differences displayed, a sample query might look like below.

select 
   t.id, 
   t.name, 
   t.birth, 
   abs(datediff(t.birth,t2.birth)) as diff 
from table t
inner join table t2 on (t.id+1) = t2.id
order by abs(datediff(t.birth,t2.birth)) desc