Ad

How To Select The First Row From A Query Into A Procedure-variable In MySQL

- 1 answer

DECLARE topScorer INT default 0;

SELECT id INTO topScorer FROM game_player
WHERE game_player.score = (SELECT max(score) FROM game_player)

A bad example but one that could easily result from naive coding... it doesn't work in my testing if multiple rows are returned, how can I get the first returned row into the variable?

Ad

Answer

Do you need just the one score?

SELECT id 
  INTO topScorer 
  FROM game_player
 WHERE game_player.score = (   SELECT max(score) as maxScore 
                                 FROM game_player 
                                 ) LIMIT 1

Update: Sir Rufo was right, the code above has now been corrected.

Ad
source: stackoverflow.com
Ad