Re: finding a 'streak'
thanks for the suggestions so far. I am trying them out. Will have to convert yours to handle the string field and increment as appropriate.
A friend sent me this which he says runs on MS SQL server but I can't figure out how to get the syntax converted to work on mySQL. Any ideas?
SELECT results.gTeamID, max( results.Wins ) AS WinningStreak
FROM (
SELECT gTeamID, gGameDate,
CASE WHEN gWLT = 'W'
THEN 1 + (
SELECT Count( * )
FROM gGames g
WHERE gWLT = 'W'
AND gTeamID = s.gTeamID
AND gGameDate > s.gGameDate
AND NOT
EXISTS (
SELECT ''
FROM gGames
WHERE gTeamID = s.gTeamID
AND gGameDate > s.gGameDate
AND gGameDate < g.gGameDate
AND gWLT <> 'W'
)
)
ELSE 0
END AS Wins,
FROM gGames s
)results
GROUP BY gTeamID
Subject
Written By
Posted
Re: finding a 'streak'
August 01, 2005 03:09PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.