Assign Top 1 to variable
-
Hi,
I would like to get the top row and assign it to a variable. How can I achieve this?
Declare @Name varchar(30)
Select @Name = Top 1 CustName
From Customer
Group By CustName
Order By CustName
I get an error stating 'Incorrect syntax near the keyword 'top'.'
Please advise. Thanks
-
TOP needs to be placed before the variable setting.
Declare @Name varchar(30)Select Top 1 @Name = CustName
From Customer
Order By CustName
I do not know why you had the GROUP BY clause in there. It is not necessary to get the first row, so I removed it.
-
Or, if you prefer to use SET for setting of one variable:
Declare @Name varchar(30)
SET @Name = (SELECT TOP 1 CustName
From Customer
Order By CustName)
-
Thanks alot!
-
Truth is, you don't even need the TOP 1 statement. If it is assigning to a variable, it will always only return one row.
-
Yes you do, you won't know which one will be returned without the top 1.
-
And if the SET statement is used (instead of the SELECT one) the statement will fail if there are more than one row returned. For that reason Vladan's version is often the better one, since the assert helps you from introducing a difficult to find bug.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply