tom.w.brannon (10/8/2013)
I believe the following statement in the article is misleading:You don’t need to do the SELECT first, but it is nice to see what you’ll be deleting.
It is required that the select section be in the delete statement because the windowing functions cannot be part of the where clause. The following DOES NOT work:
SELECT
ROW_NUMBER() OVER ( PARTITION BY FName, LName, JobTitle, Age ORDER BY LName ) R
, FName
, LName
, JobTitle
, Age
FROM DuplicateRow
WHERE ROW_NUMBER() OVER ( PARTITION BY FName, LName, JobTitle, Age ORDER BY LName ) > 1
Gives the following error:
Windowed functions can only appear in the SELECT or ORDER BY clauses.
What I meant by "You don't need to do a SELECT first" is that you can just use the DELETE query. The SELECT subquery within the DELETE query is still needed. By "first" I meant running the SELECT to see the results before running the DELETE. Both have another SELECT subquery as part of the operation.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams