SELECT TOP into variable

  • Sorry if this is a really simple question, but how do I select the top 1 into a variable? Something like this (but not this as it doesn't work!):

    SELECT @ID = TOP 1 OrderVal FROM qryCust WHERE OrderNo = '000663' AND DiscountGroup = 'XXND' AND CustID = 3

    I'd just lie to add that I am building this up as a sQL string as I have a variable mnumber and combination of parameters to include in the where clause.

    So my@ID is the output parameter from my sp_executesql.

    Many thanks

  • The top's in the wrong place

    SELECT TOP 1 @ID = OrderVal FROM qryCust

    WHERE OrderNo = '000663' AND DiscountGroup = 'XXND' AND CustID = 3

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • DOH! Many thanks!

  • Hello,

    The TOP can go to the right of the assignment too.

    like

    select @variable = ( select TOP 1 salesAmount from sales )

    Regards,

    Terry

  • In this case yes. That's assigning the results of a subquery to a variable. and only works with a single variable and column.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply