three consecutive number

[abrew]

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]

Wouldn't limit do the job?

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

[Barand]

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]

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]

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]

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]

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]

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]

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]

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]

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

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

| 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 |

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

 

$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]

wow ,,, barrand is realy good

thanx a lot ,,, this is correct 1000%