Order rows in join table mysql

Question

I have a table with date ranges. I want to look for gaps between these ranges. I've figured out that I may left join table to itself and calculate the difference.

Current table:

date_begin    date_end
2010-08-01    2010-08-15
2010-08-16    2010-08-30
2010-08-31    2010-09-12
2010-10-01    2010-10-15

I want to get:

date_begin    date_end    -   date_begin2    DATEDIFF(date_begin2 - date_end)
                              2010-08-01       
2010-08-01    2010-08-15      2010-08-16       ...
2010-08-16    2010-08-30      2010-08-31       ...
2010-08-31    2010-09-12      2010-10-01       ...
2010-10-01    2010-10-15

I use the following query:

SELECT pay1.date_begin, pay1.date_end, 
       pay2.date_begin, pay2.date_end, DATEDIFF( pay2.date_begin, pay1.date_end) 
FROM `payment_employees` AS pay1
LEFT JOIN `payment_employees` AS pay2 ON pay2.date_begin > pay1.date_end
GROUP BY pay1.date_begin
ORDER BY pay1.date_begin ASC

But the result is

pay1.date_begin date_end    pay2.date_begin date_end              difference
2010-08-01    2010-08-15    2010-08-31    2010-09-12            16  wrong
2010-08-16    2010-08-30    2010-08-31    2010-09-12            1   correct
2010-08-31    2010-09-12    2010-10-01    2010-10-15            19  correct
2010-10-01    2010-10-15    NULL              NULL

If I delete the GROUP BY I can see that there are correct rows in the result. I just can't figure out how to get them.

Is there a way to sort table before being joined?

Answer 1

If you want the rows in your tables sorted before being joined, you could replace either or both of the real tables in your join with derived tables like this...

SELECT pay1.date_begin,
       pay1.date_end, 
       pay2.date_begin,
       pay2.date_end,
       DATEDIFF( pay2.date_begin, pay1.date_end) 
    FROM (SELECT *
              FROM `payment_employees`
              ORDER BY date_begin) AS pay1
          LEFT JOIN (SELECT *
                         FROM `payment_employees`
                         ORDER BY date_end) AS pay2
             ON pay2.date_begin > pay1.date_end
    GROUP BY pay1.date_begin
    ORDER BY pay1.date_begin ASC

I'm not sure if that'll solve the substantive problem, though.