Prev: WORKS..Thanks!
Next: insert into table from excel or text file
From: Lars Zeb on 8 Jul 2008 15:52
Can anyone help me with "incorrect syntax near 'cte'"?

Thanks in advance, Lars

with cte
as
(
select
AddressID, PersonID, DateAdded,
row_number() over(partition by AddressID order by PersonID DESC) as
rn
from
dbo.Person
)
select * cte
where rn > 1;

Table definition:

create table Person (
AddressID int,
PersonID int,
DateAdded datetime)
From: Aaron Bertrand [SQL Server MVP] on 8 Jul 2008 15:59
> Can anyone help me with "incorrect syntax near 'cte'"?
>
> with cte




CTEs need to be at the very beginning of the statement, because of the way
the "WITH" keyword has been overloaded.

So, just make sure you have a semi-colon at the end of the preceding
statement. e.g. instead of:

SET NOCOUNT ON

WITH cte AS
(
...

Use:

SET NOCOUNT ON;

WITH cte AS
(
...

I have seen some use the following convention, but I am not particularly
fond of it (I have no objective evidence to back that up, either):

;WITH cte AS
(
...


From: Roy Harvey (SQL Server MVP) on 8 Jul 2008 16:13
The FROM in the outer SELECT is missing.

with cte
as
(
select
AddressID, PersonID, DateAdded,
row_number() over(partition by AddressID order by PersonID DESC) as
rn
from
dbo.Person
)
select * FROM cte
where rn > 1;

Roy Harvey
Beacon Falls, CT

On Tue, 8 Jul 2008 12:52:37 -0700 (PDT), Lars Zeb <larzeb(a)gmail.com>
wrote:

>with cte
>as
>(
>select
> AddressID, PersonID, DateAdded,
> row_number() over(partition by AddressID order by PersonID DESC) as
>rn
>from
> dbo.Person
>)
>select * cte
>where rn > 1;
From: Aaron Bertrand [SQL Server MVP] on 8 Jul 2008 16:19
Of course.

If my guess was right, the error message would have been slightly
different...

A


From: Roy Harvey (SQL Server MVP) on 8 Jul 2008 16:25
On Tue, 8 Jul 2008 16:19:13 -0400, "Aaron Bertrand [SQL Server MVP]"
<ten.xoc(a)dnartreb.noraa> wrote:

>Of course.
>
>If my guess was right, the error message would have been slightly
>different...

And if the post was typed in, instead of pasted, the problem I found
might not be the real problem at all, and your's could be the actual
answer. It is certainly easily overlooked, as I prove to myself all
too often.

Roy
 | 
Pages: 1
Prev: WORKS..Thanks!
Next: insert into table from excel or text file