CODEWITHSUNDEEP
mysqlsqlsql-server
Bryuk
Bryuk

Reputation: 3345

How to set into variable 1st value from subquery result in SQL

I'm not so good with SQL, so help me please. In my stored procedure I'm getting a parameter. Then, based on this parameter I want to get ID from another table and put this ID into my another variable, but the problem is that ID could be multiple based on this parameter.

DECLARE @RateID int;

SET @RateID = (
    Select [dbo].[Rate].RateID
    from [dbo].[Rate]
    Where [dbo].[Rate].Rate = 160       
)

Here I'm getting the error, because Subquery returned more than 1 value How to get the first value from subquery result and set to the @RateID?

Upvotes: 3

Views: 3929

Answers (4)

Josh Jay
Josh Jay

Reputation: 1250

You can use the following syntax:

Select top 1
  @RateId = RateId
from
  Rate
where
  Rate.Rate = @Rate
order by
  RateId

If you want the last, you can change the order by to:

order by
    RateId Desc

Sql Fiddle Example

Upvotes: 1

DevelopmentIsMyPassion
DevelopmentIsMyPassion

Reputation: 3591

You can use Top query like SET @RateID = ( Select Top 1 [dbo].[Rate].RateID from [dbo].[Rate] Where [dbo].[Rate].Rate = 160
)

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172588

You may want to try it like this:

DECLARE @RateID int;

SET @RateID = (
    Select Top 1 [dbo].[Rate].RateID
    from [dbo].[Rate]
    Where [dbo].[Rate].Rate = 160       
)

As in your present query there may be the case that you table has more than 1 row which satisfies the condition Rate = 160, so the select query will return more than 1 row which you cannot store in a single variable.

However in MYSQL you have to use LIMIT 1 as TOP 1 will not work in MYSQL.

Upvotes: 2

Colm Prunty
Colm Prunty

Reputation: 1620

If you only want the first result you can do 

Select TOP 1 [dbo].[Rate].RateID

in place of

Select [dbo].[Rate].RateID

Upvotes: 1

Related Questions