Post reply

SELECT TOP into variable

  • May 7, 2008 at 1:55 am

    #190020

    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

  • May 7, 2008 at 2:06 am

    #811620

    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

  • May 7, 2008 at 3:41 am

    #811640

    DOH! Many thanks!

  • May 7, 2008 at 1:28 pm

    #812033

    Hello,

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

    like

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

    Regards,

    Terry

  • May 7, 2008 at 1:36 pm

    #812040

    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

  • April 20, 2011 at 12:32 pm

    #1314161

    Thanks 🙂

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

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