I’m trying to get the last recorded loss, then count the number of wins after that to calculate a win streak.
BEGIN SET @maxDate:=(SELECT MAX(date) from game_scores where user_score<opponent_score and user_id=1); SELECT @maxDate as last_loss, COUNT(*) as streak from game_scores WHERE user_score>opponent_score and date>@maxDate and user_id=1; END
But I keep getting syntax errors at the SET @maxDate line. Am I close?
You can do this in a single shot if you join to the query for the last loss date.
Also note that, if you’ve already located the user’s last loss date, any of their records after that will be wins so your second query doesn’t need to check the user score against the opponent score.
SELECT last_recorded_loss.maxDate AS last_loss, COUNT(*) AS streak FROM game_scores INNER JOIN ( SELECT MAX(date) AS maxDate FROM game_scores WHERE user_score < opponent_score AND user_id = 1 ) last_recorded_loss ON game_scores.date > last_recorded_loss.maxDate AND game_scores.user_id = 1
Answered By – Ed Gibbs
Answer Checked By – Willingham (BugsFixing Volunteer)