Structure of my table : ID(int) | NUMBER(int) | CREATED_AT(date)
If I have three numbers : 10, 11 and 9, Can I get all rows from my table with same ratio of NUMBER values with decreasing consecutive CREATED_AT dates?
Examples :
If my numbers are : 10,11,9 ... The rows may be :
NUMBER | CREATED_AT
50 | 2012-03-18
51 | 2012-03-17
49 | 2012-03-16
Result must be "50"
If my numbers are : 50,40,60 ... The rows may be :
NUMBER | CREATED_AT
100 | 2012-02-20
90 | 2012-02-19
110 | 2012-02-18
Result must be "100"
...
I wasted a half of day for this, but I still have no Idea where to start...
P.S : I have about 5 000 rows in my table.
UPDATE
I did it with a bit of php code and a lot of mysql queries. Script execution time : ~6 seconds. [Tested on godaddy deluxe hosting] =)
I haven't tested it myself, but you can try this:
SELECT a.*,b.*,c.*
FROM table_name a
INNER JOIN table_name b
ON b.NUMBER = a.NUMBER + $range1 and DATEDIFF(b.CREATED_AT, a.CREATED_AT) = 1
INNER JOIN table_name c
ON c.NUMBER = b.NUMBER + $range2 and DATEDIFF(c.CREATED_AT, b.CREATED_AT) = 1
$range1, $range2 are variables number1 - number2 and number2 - number3.
In your example you have 10, 11 and 9. So, if t1.number = 10, then t2.number = t1.number + 1 and t3.number = t2.number - 2. This can be written into a query in this form -
SELECT t1.NUMBER
FROM (SELECT 50 AS `first`, 40 AS `second`, 60 AS `third`) AS seq
INNER JOIN `table` t1
ON `seq`.`first` <> t1.NUMBER
INNER JOIN `table` t2
ON `seq`.`second` <> t2.NUMBER
AND t1.CREATED_AT - INTERVAL 1 DAY = t2.CREATED_AT
AND CAST(t1.NUMBER AS SIGNED) - CAST(t2.NUMBER AS SIGNED) = `seq`.`first` - `seq`.`second`
INNER JOIN `table` t3
ON `seq`.`third` <> t3.NUMBER
AND t2.CREATED_AT - INTERVAL 1 DAY = t3.CREATED_AT
AND CAST(t2.NUMBER AS SIGNED) - CAST(t3.NUMBER AS SIGNED) = `seq`.`second` - `seq`.`third`;
EDIT - added the CAST to deal with negatives
This is not very efficient due to the join criteria but given such a small dataset it should be fine.