Re^5: find difference in dates/items in same column

[ccn]

How fast would be the following query?

select brand,
       id,
       date,
       datediff(day, (select max(T2.date)
                        from test as T2
                       where T1.brand = T2.brand
                             and T2.date < T1.date) ,date)
  from test as T1
 order by brand, id

I am not sure if that syntax works for MySQL. May be it must be translated to INNER JOIN

[zerocred]

Great! 120k rows in 10 seconds on MySQL on Linux and 17 seconds on MS Access
(there's no 'day' parameter mysql's DATEDIFF - when i cracked that it worked!)

select brand,
       id,
       date,
       datediff( date, (select max(T2.date)
                 from test as T2
                where T1.brand = T2.brand
                    and T2.date < T1.date) )
from test as T1
order by brand, id

Your solution is vastly superior (simpler/faster) to the one in MYSQL cookbook or any SQL forum/discussion I have seen!
Thanks that's great!