January 25, 2011 at 11:48 am
This code works just fine:
with cte
as (select userid,
createddate,
row_number() over (PARTITION by userid order by createddate) as row
from KeywordSubscriptions)
select cte.UserID, cte.CreatedDate
from cte
where row=1
Can you not put anything between the with CTE statement and the select statement? When I try the following suddenly I get the error that object 'cte' doesn't exist.
with cte
as (select userid,
createddate,
row_number() over (PARTITION by userid order by createddate) as row
from KeywordSubscriptions)
delete UniqueSubscribers
insert into UniqueSubscribers
select cte.UserID, cte.CreatedDate
from cte
where row=1
What am I doing wrong?
January 25, 2011 at 11:53 am
A CTE only affects the command right after it. You can put multiple CTEs on one command, but otherwise, it's the one right after it.
Use a temp table instead of a CTE if you need to have something in between.
Edit: Though it doesn't look like you need that to be in between. Can't you just put the delete statement before the CTE?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 25, 2011 at 11:55 am
I'll move the delete statement and give it a try.
January 25, 2011 at 11:57 am
Thank you for your help. I didn't know that about cte's. It work fine now.
January 25, 2011 at 11:59 am
CTEs are one-shot subquery definitions, only good for the next 'real' statement in the process.
They're more a way to organize subqueries into more intelligible code rather than creating unique structures for re-use. The only time a CTE does anything more than placing the FROM (SELECT * FROM x) AS y in the main query's subclause is when it does recursioning.
So, no, you can't put anything in between. All the CTE's are attempting to apply to the DELETE statement. They're no longer around when you hit the next one.
If you need a permanent CTE, you want to build a view.
Edit: Whoops, sniped by Gus, glad to see you're on your way though. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 9, 2011 at 4:59 am
create table #sample(sno int identity,student_no int, head int,task varchar(50))
insert into #sample (student_no,head,task) values(1,10,'tactical')
insert into #sample (student_no,head,task) values(10,20,'basket')
insert into #sample (student_no,head,task) values(20,40,'aerospace')
insert into #sample (student_no,head,task) values(40,10,'robot')
insert into #sample (student_no,head,task) values(10,40,'tackle')
insert into #sample (student_no,head,task) values(40,60,'trick')
if my input is student_no= 1
it shud show
student-no task
----------- -------
10 basket
20 aerospace
40 robot
10 tackle---> here is tricky thing
40 trick---->here too
using student-no input i have to get next head value and loop through it.
Anyway to acheive it?... any help pls...
i tried CTE..but it doesnt work as duplicated values occurs.... any suggestion pls?
thanks in advance
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply