## 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; ``` Last updated 22 May 2009 