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

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) 开发者_如何学C= 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

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