mysql - How to find difference between two values in one column by cross referencing against values in another column -
i have table looks this: id | machine | date | status ---------------------------- 1 | 1 | 01-01-2009 | new 1 | 1 | 01-01-2010 | busted 1 | 2 | 01-01-2010 | new 1 | 1 | 01-01-2012 | repaired i need know differences in date between each status per machine, if recent status , there no newer status machine, needs show difference between last date listed , current date: id | machine | date | status | duration --------------------------------------- 1 | 1 | 01-01-2009 | new | 1 year 1 | 1 | 01-01-2010 | busted | 2 years 1 | 2 | 01-01-2010 | new | 2 years, 2 months, 5 days 1 | 1 | 01-01-2012 | repaired | 2 months, 5 days to quite honest, don't care how new duration column displays distances between dates. attempted make query myself failed. this give values in days - select *, datediff(ifnull((select `date` machine_status ms2 ms1.machine = ms2.machine , ms1.`date` < ms2.`date` order `date` asc limit 1), current_date), ms1.`date`) machine_st