How do I find the longest consistent result set?
SELECT team1, score1, score2, team2, gameDate
FROM GAMES
WHERE team1 = 'Tottenham Hotspur'
OR team2 = 'Tottenham Hotspur'
ORDER BY gameDate ASC;
This query returns the game result and the game date (for Tottenham Hotspur), sorted by the game date.
team1 score1 score2 team2 gameDate
+-------------------------------------------------------------------------+
Tottenham Hotspur 0 0 Manchester City 2010-08-14
Stoke City 1 2 Tottenham Hotspur 2010-08-21
Tottenham Hotspur 0 1 Wigan Athletic 2010-08-28
West Bromwich Albion 1 1 Tottenham Hotspur 2010-09-11
Tottenham Hotspur 3 1 Wolverhampton Wanderers 2010-09-18
West Ham United 1 0 Tottenham Hotspur 2010-09-25
Tottenham Hotspur 2 1 Aston Villa 2010-10-02
Fulham 1 2 Tottenham Hotspur 2010-10-16
Tottenham Hotspur 1 1 Everton 2010-10-23
Manchester United 2 0 Tottenham Hotspur 2010-10-30
Bolton Wanderers 4 2 Tottenham Hotspur 2010-11-06
Tottenham Hotspur 1 1 Sunderland 2010-11-09
How to find the longest consecutive win / loss of a result set?
In the above example, the maximum 4 consecutive games (sorted by game date) have no draw result and we want to filter these games
team1 score1 score2 team2 gameDate
+-------------------------------------------------------------------------+
Tottenham Hotspur 3 1 Wolverhampton Wanderers 2010-09-18
West Ham United 1 0 Tottenham Hotspur 2010-09-25
Tottenham Hotspur 2 1 Aston Villa 2010-10-02
Fulham 1 2 Tottenham Hotspur 2010-10-16
+3
source to share
2 answers
Try the following:
SELECT tttt.* from (
SELECT ttt.*, RANK() over(order by res desc, seqcnt desc , grp desc) rnk from (
SELECT tt.*, count(*) over( partition by grp, res) as seqcnt from (
SELECT t.*, ROW_NUMBER() over(order by gameDate) - ROW_NUMBER() over(partition by res order by gameDate) as grp from (
SELECT team1, score1, score2, team2, gameDate, case when score1= score2 then 'D' else 'N' end as res
FROM GAMES WHERE team1 = 'Tottenham Hotspur' OR team2 = 'Tottenham Hotspur'
) t
) tt
) ttt
) tttt WHERE rnk = 1 order by gameDate
+4
source to share