SQL: Windowed functions can only appear in the SELECT or ORDER BY clauses

Use a CTE. Problem could occur in

WITH myCTE AS
(
SELECT WO, QuoteNo, EntryNo = ROW_NUMBER() OVER (PARTITION BY QuoteNo ORDER BY WO)
FROM dbo.Orders2006
WHERE QuoteNo IS NOT NULL
)

UPDATE dbo.Orders2006
SET EntryNo = myCTE.EntryNo
FROM dbo.Orders2006
INNER JOIN myCTE ON myCTE.WO = dbo.Orders2006.WO

 

With thanks to:

http://stackoverflow.com/questions/1466963/sql-row-number-function-in-where-clause

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: