three consecutive number

abrew · July 26, 2012

i have table database like this

Dayset Totrain

1 12

2 0

3 21

4 33

5 25

6 24

i need to get the 4th "dayset" after 3 consecutive Totrain > 20

for the data abobve ,,,

3 consecutive TotRain > 20 is ; 21 ,,, 33 ,,, 25

so i need to show that the 4th dayset is " dayset 6 "

any sugestion please ,,, ???

Drummin · July 26, 2012

Wouldn't limit do the job?

$sql = "SELECT Dayset,Totrain FROM tablename WHERE TotRain>'20' LIMIT 3";

Barand · July 26, 2012

this should do it

my data

+--------+---------+

| dayset | totrain |

+--------+---------+

| 1 | 12 |

| 2 | 0 |

| 3 | 21 |

| 4 | 33 |

| 5 | 25 |

| 6 | 24 |

| 7 | 18 |

| 8 | 21 |

| 9 | 22 |

| 10 | 25 |

| 11 | 19 |

+--------+---------+

the query

SELECT r.dayset, a.dayset as A, b.dayset as B, c.dayset as C
FROM rain r
LEFT JOIN rain as a ON a.dayset = r.dayset-1 AND a.totrain > 20
LEFT JOIN rain as b ON b.dayset = a.dayset-1 AND b.totrain > 20
LEFT JOIN rain as c ON c.dayset = b.dayset-1 AND c.totrain > 20
WHERE a.dayset IS NOT NULL
    AND b.dayset IS NOT NULL
    AND c.dayset IS NOT NULL

my results

+--------+------+------+------+

| dayset | A | B | C |

+--------+------+------+------+

| 6 | 5 | 4 | 3 |

| 7 | 6 | 5 | 4 |

| 11 | 10 | 9 | 8 |

+--------+------+------+------+

abrew · July 29, 2012

Thx Drummin 4 u'r clue ,,, limit not counting the "3 consecutive value" that i need

Thx Barrand ,,, u'r clue is very good

but i'm wrong gave u example of my table becouse the "dayset" is not time series

but it's dayset is only 1-2-3 (it's date 1-10, 11-20, 21-end) it is actualy like this (for data in Year 2010):

ID Year Month dayset TotRain

1220 2010 1 1 12

1220 2010 1 2 9

1220 2010 1 3 22

1220 2010 2 1 25

1220 2010 2 2 45

1220 2010 2 3 15

1220 2010 3 1 55

1220 2010 3 2 40

1220 2010 3 3 21

1220 2010 4 1 10

1220 2010 4 2 33

1220 2010 4 3 31

| | | | |

1220 2010 12 3 18

so u'r clue is not working with my problem ,,, i think becaouse the dayset is not time series

any sugestion please ,,, ???

anyway thank you for helping

abdfahim · July 29, 2012

thanks Drummin 4 u'r clue ,,, limit not counting the "3 consecutive value" that i need

May be you can rewrite query like

$sql = "SELECT Totrain FROM tablename WHERE TotRain>'20' ORDER BY `ID` ASC, `Year` ASC, `Month` ASC, `dayset` ASC LIMIT 3

abrew · July 29, 2012

thanx abdbuet ,,,

the code you give is not what i meant ,,,

becouse dayset not consecutively grouped if we make it by your code

the result is yes the 3 totrain that's > '20' but it's grouped not consecutively

i need the dayset to be consecutive

thanx

other sugestion please ,,,,

Barand · July 29, 2012

I've changed it to work with dates instead of just the day. Use correct datetypes so you can use date arithmetic easily.

DATA

+---------+------------+

| totrain | dayset |

+---------+------------+

| 12 | 2012-02-26 |

| 0 | 2012-02-27 |

| 21 | 2012-02-28 |

| 33 | 2012-02-29 |

| 25 | 2012-03-01 |

| 24 | 2012-03-02 |

| 18 | 2012-03-03 |

| 21 | 2012-03-04 |

| 22 | 2012-03-05 |

| 25 | 2012-03-06 |

| 19 | 2012-03-07 |

+---------+------------+

SELECT r.dayset, a.dayset as A, b.dayset as B, c.dayset as C
FROM rain r
LEFT JOIN rain as a ON a.dayset = r.dayset - INTERVAL 1 DAY AND a.totrain > 20
LEFT JOIN rain as b ON b.dayset = a.dayset - INTERVAL 1 DAY AND b.totrain > 20
LEFT JOIN rain as c ON c.dayset = b.dayset - INTERVAL 1 DAY AND c.totrain > 20
WHERE a.dayset IS NOT NULL
    AND b.dayset IS NOT NULL
    AND c.dayset IS NOT NULL

RESULTS

+------------+------------+------------+------------+

| dayset | A | B | C |

+------------+------------+------------+------------+

| 2012-03-02 | 2012-03-01 | 2012-02-29 | 2012-02-28 |

| 2012-03-03 | 2012-03-02 | 2012-03-01 | 2012-02-29 |

| 2012-03-07 | 2012-03-06 | 2012-03-05 | 2012-03-04 |

+------------+------------+------------+------------+

abrew · July 29, 2012

Thx again barran ,,,

but still the problem is

dayset is not a date ( that is not time series not like a DATE) but

the dayset is just has 3 number repeatedly like this <i attach the example dataset>

Month dayset

1 1

1 2

1 3

2 1

2 2

2 3

3 1

3 2

3 3

| |

12 1

12 2

12 3

do you got what i mean ,,,

sorry for my bad explanation ,,,

sugestion please ,,,,

abrew · July 30, 2012

i think i can make this ,,,

IF r.dayset=1 
{
SELECT r.ID, r.Year, r.Month, r.dayset, r.TotRain, a.dayset as A, b.dayset as B, c.dayset as C
FROM dasharian r
LEFT JOIN dasharian as a ON a.dayset = r.dayset AND a.TotRain > 10
LEFT JOIN dasharian as b ON b.dayset = a.dayset+1 AND b.Month=a.Month AND b.TotRain > 10
LEFT JOIN dasharian as c ON c.dayset = b.dayset+1 AND c.Month=b.Month AND c.TotRain > 10
WHERE r.Year='2010'
    AND a.dayset IS NOT NULL
    AND b.dayset IS NOT NULL
    AND c.dayset IS NOT NULL
}
elseif r.dayset=2
{
SELECT r.ID, r.Year, r.Month, r.dayset, r.TotRain, a.dayset as A, b.dayset as B, c.dayset as C
FROM dasharian r
LEFT JOIN dasharian as a ON a.dayset = r.dayset AND a.TotRain > 10
LEFT JOIN dasharian as b ON b.dayset = 3 AND b.Month=a.Month AND b.TotRain > 10
LEFT JOIN dasharian as c ON c.dayset = 1 AND c.Month=b.Month+1 AND c.TotRain > 10
WHERE r.Year='2010'
    AND a.dayset IS NOT NULL
    AND b.dayset IS NOT NULL
    AND c.dayset IS NOT NULL
}

elseif r.dayset=3
{
SELECT r.ID, r.Year, r.Month, r.dayset, r.TotRain, a.dayset as A, b.dayset as B, c.dayset as C
FROM dasharian r
LEFT JOIN dasharian as a ON a.dayset = r.dayset AND a.TotRain > 10
LEFT JOIN dasharian as b ON b.dayset = 1 AND b.Month=a.Month+1 AND b.TotRain > 10
LEFT JOIN dasharian as c ON c.dayset = 2 AND c.Month=b.Month+1 AND c.TotRain > 10
WHERE r.Year='2010'
    AND a.dayset IS NOT NULL
    AND b.dayset IS NOT NULL
    AND c.dayset IS NOT NULL
}

but it's wrong ,,,

is anybody can show me where it's WRONG ???

any help please ,,,, ???

Psycho · July 30, 2012

Maybe I'm missing something, but based what I see the query would be as simple as this.

SELECT *
FROM item_price
WHERE Dayset >= (SELECT Dayset FROM item_price WHERE Totrain > 20 LIMIT 1)
ORDER BY Dayset
LIMIT 3

Barand · July 30, 2012

There is no suitable consecutive data, so a change of tack - use an array so we have consecutive index.

I used the data you posted earlier

+------+------+-------+--------+---------+

+------+------+-------+--------+---------+

| 1220 | 2010 | 1 | 1 | 12 |

| 1220 | 2010 | 1 | 2 | 9 |

| 1220 | 2010 | 1 | 3 | 22 |

| 1220 | 2010 | 2 | 1 | 25 |

| 1220 | 2010 | 2 | 2 | 45 |

| 1220 | 2010 | 2 | 3 | 15 |

| 1220 | 2010 | 3 | 1 | 55 |

| 1220 | 2010 | 3 | 2 | 40 |

| 1220 | 2010 | 3 | 3 | 21 |

| 1220 | 2010 | 4 | 1 | 10 |

| 1220 | 2010 | 4 | 2 | 33 |

| 1220 | 2010 | 4 | 3 | 31 |

+------+------+-------+--------+---------+

$sql = "SELECT year, month, dayset, totrain
        FROM rain2
        WHERE year = 2010
        ORDER BY year, month, dayset
        ";
$rain = array();

$res = mysql_query($sql) or die($sql . mysql_error());
while ($row = mysql_fetch_assoc($res)) {
    $rain[] = $row;
}

$results = array();
for ($i=4, $k=count($rain); $i<$k; ++$i) {
    if ($rain[$i-1]['totrain'] > 20 && $rain[$i-2]['totrain'] > 20 && $rain[$i-3]['totrain'] > 20  ) {
        $results[]  = $rain[$i];
    }
}

echo '<pre>'.print_r($results, 1).'</pre>';

RESULTS:

Array

(

0 => Array

(

[year] => 2010

[month] => 2

[dayset] => 3

[totrain] => 15

)

1 => Array

(

[year] => 2010

[month] => 4

[dayset] => 1

[totrain] => 10

)

abrew · July 30, 2012

wow ,,, barrand is realy good

thanx a lot ,,, this is correct 1000%

Sign In

three consecutive number

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information