Calculating streak in table

[karimali831]

Hello

 

Please take a look at attatchment:

 

Lets say for example I wanted to check the streak of clan1 (column) = 9 (rows)

The streak would be 5 because each row has score1 greater than score2.

 

So I want to calculate the streak of the value in clan1 column that has score1 greater than score2 in rows, if score2 is greater than score1 in a row then the streak breaks if that makes sense? let me know if I need to re-phrase.

 

Secondly, if there are two streaks, I want it to calculate the highest one.

 

Thanks for any help !!

 

 

[attachment deleted by admin]

[jcbones]

SELECT COUNT(matchID) as count, clan1 from {$table} WHERE score1 > score2 group by clan1 order by count DESC LIMIT 1

 

Try that, and let me know how it goes.  Be sure and add the table name into that query.

[karimali831]

On my table the longest streak is only 1, and is outputting this correctly.

And.. I must use a while loop for what I want to achieve?

 

$streak = safe_query("SELECT COUNT(matchID) as streak, clan1 FROM ".PREFIX."cup_matches WHERE clan1='$teamID' AND score1 > score2 GROUP BY clan1 ORDER BY streak DESC LIMIT 1");
while($sk1=mysql_fetch_array($streak)) { $challenger_streak = $sk1['streak']; }

    if(empty($challenger_streak)) $streak = 0;
    else $streak = $challenger_streak;

 

Thanks alot for your help!

[jcbones]

No need to use a while loop, because the query is limited to return 1 row.

 

If you want to show all the streaks, you can use a while loop, and remove the "LIMIT 1" from the end of the query.

[karimali831]

Thanks again!

 

And my second question is, I would like to pick a row that has max SUM in "score1" column:

 

E.g.

SELECT clan1 FROM ".PREFIX."cup_matches WHERE score1='(MAX SUM)");

 

Can this be done?

[jcbones]

You want the max score in score1?

 

SELECT clan1 FROM {$table} ORDER BY score1 DESC LIMIT 1

 

OR,

 

You want the clan with the highest score accumulation in score1?

 

SELECT clan1, SUM(score1) as score FROM {$table} ORDER BY score DESC LIMIT 1

[karimali831]

Thank you again!

Works