EXPLAIN EXTENDED

From Stack Overflow:

Given the following table:

ID	State	Date
12	1	2009-07-16 10:00
45	2	2009-07-16 13:00
67	2	2009-07-16 14:40
77	1	2009-07-16 15:00
89	1	2009-07-16 15:30
99	1	2009-07-16 16:00

, how can I group by the field state, while still maintaining the borders between the state changes?

I need MIN(id), MIN(date) and COUNT(*) for each group, and this is expected:

ID	State	Date	Count
12	1	2009-07-16 10:00	1
45	2	2009-07-16 13:00	2
77	1	2009-07-16 15:00	3

Though a pure set-based solution exists, in MySQL, this can be easily done using session variables, which is more efficient.

Let's create a sample table:

Read the rest of this entry »