get list of same numbers by date difference

Question

I have mysql table with two columns as below

number         date

1             2013-08-11

1             2013-08-15

1             2013-07-09

2             2013-08-18

3             2013-08-06

3             2013-08-21

Now I want all numbers for which date difference within 5 days for the same number.

Thanks all

Answer 1

SELECT DISTINCT number
FROM   my_table a JOIN my_table b USING (number)
WHERE  b.date BETWEEN a.date AND a.date + INTERVAL 5 DAY