## Winning streaks

### from the Artful Common Queries page

 Given a table of IDs and won-lost results, how do we find the longest winning streak? ``` drop table if exists results; create table results(id int,result char(1)); insert into results values (1,'w'),(2,'l'),(3,'l'),(4,'w'),(5,'w'),(6,'w'),(7,'l'),(8,'w'),(9,'w'); select * from results; +------+--------+ | id   | result | +------+--------+ |    1 | w      | |    2 | l      | |    3 | l      | |    4 | w      | |    5 | w      | |    6 | w      | |    7 | l      | |    8 | w      | |    9 | w      | +------+--------+ ``` We can find streaks of two with a left join on a.id=b.id+1. To count streak lengths, initialise a counter to 0 then increment it for every hit: ``` set @count=0; select a.id, a.result, b.result, @count := IF(a.result = b.result, @count + 1, 1) as Streak from results a  left join results b on a.id = b.id + 1  where a.result = 'w'; ``` The longest winning streak is the longest such streak found: ``` set @count=0; select MAX(@count:=IF(a.result = b.result, @count + 1, 1)) as LongestStreak from results a  left join results b on a.id = b.id + 1  where a.result = 'w'; +---------------+ | LongestStreak | +---------------+ |             3 | +---------------+ ``` That solution is from a response by Jon Roshko to a question by Ed Ball on the MySQL Newbie Forum. Scott Noyes points out that our query pattern for sequence starts and ends also works for winning streaks: ``` SELECT MIN( c.id ) - a.id + 1 as LongestStreak FROM results AS a LEFT JOIN results AS b ON a.id = b.id + 1 AND b.result = 'w' LEFT JOIN results AS c ON a.id <= c.id AND c.result = 'w' LEFT JOIN results AS d ON c.id = d.id - 1 AND d.result = 'w' WHERE   a.result = 'w'   AND b.id IS NULL   AND c.id IS NOT NULL   AND d.id IS NULL GROUP BY a.id ORDER BY LongestStreak DESC LIMIT 1; ```