mysql streak

[Destramic]

hey guys i have a table called match_results and looks like this

 

match_results

--------------------------

league_result_id

league_match_id

team_id

result

-------------------------

 

now what i want to be able to do is when selecting the results is get the steak where result = win

 

if anyone could help or have a site on how this can be done please...thank you

[fenway]

Well, you haven't told us where the sortorder column is.

[Destramic]

sorry i dont see what you mean by sortorder column?...you mean the column i want to sort and order?

[Destramic]

what im basical after is the current continuous streak...so if a team has won thier last 3 games then streak is 3...but if they loss or draw then the streak ends and equals 0

[fenway]

How are we to know which ones are the "last 3"?

[Destramic]

oh sorry that is where i should have a date column?...im still trying to design this table

[fenway]

oh sorry that is where i should have a date column?...im still trying to design this table

Well, don't you necessarily need to know when each game was played?

[Destramic]

sorry about the delay...

 

here is the query i have and i want to add a streak column to it so if you can help or advise me what to do...i would like to add it to this query really if possible

 

SET @rank := 0;
SET @points := 0;
SET @drawing := 0;

SELECT *,
                 @rank := IF(@points = points, IF(@points = '0', @rank + 1, @rank), @rank + 1), 
                         @points := points,
                         @drawing := IF(@points = points, IF(@points = '0', @drawing = '0', @drawing = '1'), @drawing = '0'),
                         IF(@drawing = '1', @rank + 1, @rank) as rank,
                         @drawing := '0'
                  FROM(
                  SELECT t.team_id,
                         t.team_name, 
                         COUNT(r.league_match_result_id) AS 'matches_played',
                         SUM(IF(r.result='Win', 1, 0)) AS `wins`,
                         SUM(IF(r.result='Loss', 1, 0)) AS `losses`,
                         SUM(IF(r.result='Draw', 1, 0)) AS `draws`,
                         SUM(IF(r.result='Win', 3, IF(r.result='Draw', 1, IF(r.result='Loss', 0, 0)))) AS `points`
                  FROM teams t
                  LEFT JOIN league_match_results r ON r.team_id = t.team_id
                  LEFT JOIN team_leagues tl ON tl.team_id = t.team_id
                  LEFT JOIN leagues l ON l.league_name = 'test'
                  LEFT JOIN games g ON g.game_abbreviation = 'CSS'
                  GROUP BY t.team_id
                  ORDER BY points DESC, t.team_name) AS x

[fenway]

That's the same query as from this topic?

 

And you still didn't answer my question.

[Destramic]

well this is the query i have been working on and yeah the query from the same topic....and sorry yes that table now has a date included now

[fenway]

See here.

[Destramic]

thanks fenway...a very useful site...now ive tried to do what i think is right but the query isnt working at all

 

the error im getting is

#1242 - Subquery returns more than 1 row

 

SET @rank := 0;
SET @points := 0;
SET @tie := 0; 
SET @win_streak := 0;

SELECT *,
                 @rank := IF(@points = points, IF(@points = '0', @rank + 1, @rank), @rank + 1), 
                         @points := points,
                         @tie := IF(@points = points, IF(@points = '0', @tie = '0', @tie = '1'), @tie = '0'),
                         IF(@tie = '1', @rank + 1, @rank) as rank,
                         @tie := '0',
                         @win_streak := '0'
                  FROM(
                  SELECT t.team_id,
                         t.team_name, 
                         
                          (SELECT 
                          IF(lmr.result='Win', @win_streak + 1, IF(lmr.result='Draw', @win_streak = '0', IF(lmr.result='Loss', @win_streak = '0', 0))) AS `win_streak`
                          FROM league_match_results lmr
                          LEFT JOIN league_matches lm ON lm.league_match_id = lmr.league_match_id
                          WHERE lmr.team_id = t.team_id
                          ORDER BY lm.date DESC),

                         COUNT(r.league_match_result_id) AS 'matches_played',
                         SUM(IF(r.result='Win', 1, 0)) AS `wins`,
                         SUM(IF(r.result='Loss', 1, 0)) AS `losses`,
                         SUM(IF(r.result='Draw', 1, 0)) AS `draws`,
                         SUM(IF(r.result='Win', 3, IF(r.result='Draw', 1, IF(r.result='Loss', 0, 0)))) AS `points`
                         FROM teams t
                  LEFT JOIN league_match_results r ON r.team_id = t.team_id
                  LEFT JOIN team_leagues tl ON tl.team_id = t.team_id
                  LEFT JOIN leagues l ON l.league_name = 'test'
                  LEFT JOIN games g ON g.game_abbreviation = 'CSS'
                  GROUP BY t.team_id
                  ORDER BY points DESC, t.team_name) AS x

 

the query works without

 

    (SELECT 
                          IF(lmr.result='Win', @win_streak + 1, IF(lmr.result='Draw', @win_streak = '0', IF(lmr.result='Loss', @win_streak = '0', 0))) AS `win_streak`
                          FROM league_match_results lmr
                          LEFT JOIN league_matches lm ON lm.league_match_id = lmr.league_match_id
                          WHERE lmr.team_id = t.team_id
                          ORDER BY lm.date DESC),

 

can you please help me

[Destramic]

ok i sort of go in work...but...it returns the team as having 1 streak...but it has no win result if you can help please

 

SELECT MAX( IF( lmr.result = 'Win', @win_streak +1, IF( lmr.result = 'Draw', @win_streak = '0', IF( lmr.result = 'Loss', @win_streak = '0', @win_streak = '0' ) ) ) ) AS `win_streak`
FROM league_match_results lmr
LEFT JOIN league_matches lm ON lm.league_match_id = lmr.league_match_id
WHERE lmr.team_id =1
ORDER BY lm.date DESC

[fenway]

Of course it returns more than one row -- that's the whole point -- first get the streak example to work, then merge it back.

 

Since there's no sample data, I can't help you.

[Destramic]

ok ive got it working now finnaly...the only problem i do have if you can help is that the win_streak column returns null on some cases...not ive tried to put WHEN NULL THEN 0 in the case but that doesnt do nothing...still returns null...thanks for your help fenway

 

SET @rank := 0;
SET @points := 0;
SET @tie := 0; 
SET @win_streak := 0;

SELECT *,
                 @rank := IF(@points = points, IF(@points = '0', @rank + 1, @rank), @rank + 1), 
                         @points := points,
                         @tie := IF(@points = points, IF(@points = '0', @tie = '0', @tie = '1'), @tie = '0'),
                         IF(@tie = '1', @rank + 1, @rank) as rank,
                         @tie := '0'

                  FROM(
                      SELECT t.team_id,
                         t.team_name, 
                (SELECT

                        MAX(@win_streak := CASE lmr.result
WHEN 'Win'
THEN @win_streak +1
ELSE 0
END)

                          FROM league_match_results lmr
                          LEFT JOIN league_matches lm ON lm.league_match_id = lmr.league_match_id
                          WHERE lmr.team_id = t.team_id                       
           ORDER BY lm.date DESC) AS `win_streak`,
                     
                         COUNT(r.league_match_result_id) AS 'matches_played',
                         SUM(IF(r.result='Win', 1, 0)) AS `wins`,
                         SUM(IF(r.result='Loss', 1, 0)) AS `losses`,
                         SUM(IF(r.result='Draw', 1, 0)) AS `draws`,
                         SUM(IF(r.result='Win', 3, IF(r.result='Draw', 1, IF(r.result='Loss', 0, 0)))) AS `points`
                         FROM teams t
                  LEFT JOIN league_match_results r ON r.team_id = t.team_id
                  LEFT JOIN team_leagues tl ON tl.team_id = t.team_id
                  LEFT JOIN leagues l ON l.league_name = 'Counter-Strike Europeon Team Death Match'
                  LEFT JOIN games g ON g.game_abbreviation = 'CSS'
                  GROUP BY t.team_id
                  ORDER BY points DESC, t.team_name) AS x

[fenway]

That's a really long query -- impossible to debug without data.

[Destramic]

why what other way do you think i should do it?...but like i said some of the teams have no records of matches and they havent had any so the win_streak reutrns null....can i set that to 0 somehow please?

[fenway]

Why can't you just fix this after the query runs? If NULL is always 0, that should be easy.

 

If you want me to actually run that crazy query, you'll need to provide the raw queries to make the tables and populate them with your data.

[Destramic]

yeah i suppose i could put

 

if (empty($row['win_steak']))
{
     echo "0";
}
else
{
   echo $row['win_streak'];
}

 

but i'd much prefer to be able to do it in the query...and what do you mean by crazy query? haha...i mean how else should i do what i want to do in a query?

[fenway]

You need to wrap the MAX() call if IFNULL(), not the case.

[Destramic]

sorry you are gonna have to bear with me and explain a bit more cause im no pro with mysql

 

wrap max()?

aslso i tried IFNULL(@win_streak, 0) but it didnt work.

 

and you want me to get rid of the case?

 

                        MAX(@win_streak := CASE lmr.result
WHEN 'Win'
THEN @win_streak +1
ELSE 0
END) AS `win_streak`

[fenway]

IFNULL(                        MAX(@win_streak := CASE lmr.result

WHEN 'Win'

THEN @win_streak +1

ELSE 0

END), 0 ) AS `win_streak`

[Destramic]

fenway you are the main man...thanks alot