CODEWITHSUNDEEP
mysqlstored-procedures
Mr. Boy
Mr. Boy

Reputation: 63748

How to select the first row from a query into a procedure-variable in MySQL

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?

Upvotes: 1

Views: 5155

Answers (4)

Wajid khan
Wajid khan

Reputation: 872

1) declare variable in SP: 

declare @CourseID int 
set @CourseID = 0

2) We need two query first for assign ID to variable and inner query for select only Top 1 record form table. In where clause of first query we compare ID with result of inner query:

SELECT @CourseID = ID FROM Course ID = ( Select Top 1 ID from Course )

3) Now check Variable value:

 if(@CourseID > 0 )
    Begin
           //This mean ID of first row is assigned to CourseID
    End
    Else 
    Begin
      //Can't found any record.
    End

Upvotes: 0

Jason
Jason

Reputation: 15335

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.

Upvotes: 2

Sir Rufo
Sir Rufo

Reputation: 19106

Use LIMIT x to ensure you are receiving only x rows from your query.

In this case you only want to get 1 row: 

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

SQL Fiddle DEMO

As a working alternative you can also use this

  SELECT id 
    INTO topScorer 
    FROM game_player
ORDER BY score DESC
   LIMIT 1

SQL Fiddle DEMO

Upvotes: 1

Dipali Nagrale
Dipali Nagrale

Reputation: 520

Apply limit in sub query to get only 1 value from sub query

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

Or to get multiple value from sub query used below one:

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

Upvotes: 1

Related Questions