Select Top 1 in variable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Top 1 in variable

Author

Topic

Ciupaz
Posting Yak Master

232 Posts

Posted - 2010-04-26 : 05:32:24

Hello,
having a simple table like this:

Create table dbo.Table1 (Location int, Value float)
go
insert dbo.Table1 values(10,0)
insert dbo.Table1 values(12,34.5)
insert dbo.Table1 values(13,12.5)
insert dbo.Table1 values(15,35.5)

why this snippet does not work?

declare @FirstLocationNotZero int
select @FirstLocationNotZero = top 1 Location from dbo.Table1
where value<>0
print @FirstLocationNotZero

I should obtain 2, instead of:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'top'.

Thanks a lot.

Luigi

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-26 : 05:41:39

declare @FirstLocationNotZero int
select top 1 @FirstLocationNotZero = Location
from dbo.Table1
where value<>0

print @FirstLocationNotZero

Ciupaz
Posting Yak Master

232 Posts

Posted - 2010-04-26 : 05:45:54

Thank you so much.

Luigi

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-26 : 05:49:03

You are welcome

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-26 : 06:34:18

please keep in mind unless you use an order by the above statement just gives you a random Location
field values where value<>0. it wont necessarily be first value in your sample output (12)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ciupaz
Posting Yak Master

232 Posts

Posted - 2010-04-26 : 10:20:11

Ok, thank you Visakhm.

Luigi

quote:
Originally posted by visakh16

please keep in mind unless you use an order by the above statement just gives you a random Location
field values where value<>0. it wont necessarily be first value in your sample output (12)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-26 : 23:31:31

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources