I'm trying to get the last recorded loss, then count the number of wins after that to calculate a win streak.
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;
But I keep getting syntax errors at the SET @maxDate line. Am I close?
Best How To :
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.
last_recorded_loss.maxDate AS last_loss,
COUNT(*) AS streak
INNER JOIN (
SELECT MAX(date) AS maxDate
WHERE user_score < opponent_score
AND user_id = 1
ON game_scores.date > last_recorded_loss.maxDate AND
game_scores.user_id = 1