ROW_NUMBER() problem

[Yulianto]

When using SELECT ROW_NUMBER() OVER (ORDER BY id) AS [RowNumber] FROM table where RowNumber > 1 It returns error 'Invalid column name 'RowNumber'. What is the problem here? I'm using SQL Server Express 2005. Thanks.

---

Don't work hard, but work effectively. Web hosting | invest money | Option strategy

[Lost User]

You added 'RowNumber' as a column-name alias. Those can be used in the ORDER BY clause, but can't be referenced in the WHERE part. Since you're using SQL 2005, you can bypass this by using a temporary query, selecting into a new CTE first;

SELECT * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY ID) AS [RowNumber]
FROM [YourTableNameGoesHere]
) AS CTE_TMP
WHERE [RowNumber] < 10

Enjoy :)

I are troll :)