Solved

rown number Delete

Posted on 2011-09-09
6
289 Views
Last Modified: 2012-05-12

What is wrong with my delete

getting this error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'sp'.

Delete from (
Select *,
Row_NUmber() over (partition by EmailAddress order by CreationTime Desc) rn 
from OpenClickThru where EmailAddress is Not NULL
) sp
Where sp.rn > 1 ;

Open in new window

0
Comment
Question by:Leo Torres
  • 2
  • 2
  • 2
6 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 250 total points
ID: 36514248
Try this:


;with cte as
(
Select *, Row_NUmber() over (partition by EmailAddress order by CreationTime Desc) rn
from OpenClickThru)
delete from cte
where EmailAddress is Not NULL and rn >1
0
 
LVL 22

Assisted Solution

by:Thomasian
Thomasian earned 250 total points
ID: 36515011
To correct the syntax of your query:
Delete sp
FROM (Select *,
			Row_NUmber() over (partition by EmailAddress order by CreationTime Desc) rn 
		from OpenClickThru where EmailAddress is Not NULL
	) sp
Where sp.rn > 1 

Open in new window

0
 
LVL 8

Author Comment

by:Leo Torres
ID: 36515532
dqmq: yours worked fine

But I am still curious because I would like to know why
Thomasian: did not work this was what I was tring to do there must be a way to do this with out CTE right?

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'Select'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 22

Expert Comment

by:Thomasian
ID: 36515537
Did you run the query as is? Maybe you left out the word "FROM" since I get the same error without it.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 36515687
Ha that's what it was..

Thanks
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36516472
Original query not missing "FROM", missing "SP" before the "FROM". :>)
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to base a filter depending on fields contents? 15 57
Help Extract Specific in SQL 8 34
Please help for the below sql query. 1 24
SQL Server 2012 r2 Make faster Temp Table 17 101
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now