Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
PerlMonks  

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

by ccn (Vicar)
on Nov 15, 2008 at 07:46 UTC ( #723780=note: print w/ replies, xml ) Need Help??


in reply to Re^4: find difference in dates/items in same column
in thread find difference in dates/items in same column

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


Comment on Re^5: find difference in dates/items in same column
Download Code
Re^6: find difference in dates/items in same column
by zerocred (Beadle) on Nov 15, 2008 at 22:04 UTC
    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!

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://723780]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (4)
As of 2014-09-23 04:40 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (210 votes), past polls