TSQL top 1 parameter assignment issue

Question

I'm trying to get the MODE average for a set of zipcodes (zip code with most entries in a particular upload set). I want to make this a scalar function, instead of a stored procedure.

Why does this work:

Select Top 1 LocationZip
from UploadSetZipCodeCount
where WorkOrderSet = 31
Order by ZipCount desc

But not this:

Select @setbasezip= Top 1 LocationZip
from UploadSetZipCodeCount
where WorkOrderSet = 31
Order by ZipCount desc

My declaration type is correct for the LocationZip field.

Answer 1

Just a syntax issue... select top 1 should come before the variable assignment:

Select Top 1 @setbasezip = LocationZip
from UploadSetZipCodeCount
where WorkOrderSet = 31
Order by ZipCount desc