Assigning variables with SELECT statements

Hi,
The syntax
SELECT @.varname = colname FROM table WHERE ...
is valid in SQL server, but I am unable to use the syntax
SELECT @.varname = TOP 1 colname FROM table WHERE ...
which would be useful if (for example) getting the most recent index number
from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
I can use a workaround such as
SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
As I have a workaround that works well, I'm not too concerned about this -
just wondering if I'm missing something with the syntax that causes my
second example to fail.
John.
Try:
SELECT TOP 1 @.varname = colname FROM table WHERE ...
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:%23yfS71clEHA.3372@.TK2MSFTNGP09.phx.gbl...
Hi,
The syntax
SELECT @.varname = colname FROM table WHERE ...
is valid in SQL server, but I am unable to use the syntax
SELECT @.varname = TOP 1 colname FROM table WHERE ...
which would be useful if (for example) getting the most recent index number
from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
I can use a workaround such as
SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
As I have a workaround that works well, I'm not too concerned about this -
just wondering if I'm missing something with the syntax that causes my
second example to fail.
John.
|||John,
You were so close...
SELECT TOP 1 @.varname = colname FROM table WHERE ...
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:%23yfS71clEHA.3372@.TK2MSFTNGP09.phx.gbl...
> Hi,
> The syntax
> SELECT @.varname = colname FROM table WHERE ...
> is valid in SQL server, but I am unable to use the syntax
> SELECT @.varname = TOP 1 colname FROM table WHERE ...
> which would be useful if (for example) getting the most recent index
number
> from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
> I can use a workaround such as
> SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
> As I have a workaround that works well, I'm not too concerned about this -
> just wondering if I'm missing something with the syntax that causes my
> second example to fail.
> John.
>
|||>> ...but I am unable to use the syntax
SELECT @.varname = TOP 1 colname FROM table WHERE ... <<
The variable should be immediately before the column name like:
SELECT TOP 1 @.varname = colname FROM table ...
Anith
|||How about this method:
SELECT TOP 1 @.varname = colname FROM table WHERE ...
Keith
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:%23yfS71clEHA.3372@.TK2MSFTNGP09.phx.gbl...
> Hi,
> The syntax
> SELECT @.varname = colname FROM table WHERE ...
> is valid in SQL server, but I am unable to use the syntax
> SELECT @.varname = TOP 1 colname FROM table WHERE ...
> which would be useful if (for example) getting the most recent index
number
> from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
> I can use a workaround such as
> SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
> As I have a workaround that works well, I'm not too concerned about this -
> just wondering if I'm missing something with the syntax that causes my
> second example to fail.
> John.
>
|||Keith Kratochvil wrote:
> How about this method:
> SELECT TOP 1 @.varname = colname FROM table WHERE ...
Thanks all - much appreciated.
John.