Order by column alias

I'm using SQL Server 2005 and are having some troubble with sorting a paged result set. I'm using the OVER Clause to achieve the sorting and paging and have the following query:

1WITH ProjectListAS2(3SELECT4Id,5Name,6Created,7(SELECTCOUNT(*)FROM UserProjectsWHERE ProjectId = p.Id)AS NumberOfUsers,8 ROW_NUMBER()OVER (ORDER BY Id)AS'RowNumber'9FROM Projects p10)11SELECT *12FROM ProjectList13WHERE RowNumberBETWEEN 50AND 60;

This works fine, and give me the results i want. The problem occurs when I want to sort by "NumberOfUsers" which is the results of a sub query.
When i say "ORDER BY NumberOfUsers" instead of Id on line 8, I get the following error:

Msg 207, Level 16, State 1, Line 10
Invalid column name 'NumberOfUsers'.

I read this in the documentation:

When used in the context of a ranking window function, <ORDER BY Clause> can only refer to columns made available by the FROM clause. An integer cannot be specifiedto represent the position of the name or alias of a column in the select list. <ORDER BY Clause> cannot be used with aggregate window functions.

So this means that what I'm trying to do is not possible. How can I then sort by NumberOfUsers? Is there any other way to achieve this

Hi i am not 100% sure if its work but try

select * from ProjectList where RowNumber between 50 and 60

order by 4

|||

Maybe you can try something like this with another inner query?

WITH ProjectListAS
(
Select RR.*,
ROW_NUMBER()OVER (ORDER BY NumberOfUsers)AS'RowNumber'
from (SELECT
Id,
Name,
Created,
(SELECTCOUNT(*)FROM UserProjectsWHERE ProjectId = p.Id)AS NumberOfUsers,
FROM Projects p) RR
)
SELECT *
FROM ProjectList
WHERE RowNumberBETWEEN 50AND 60;

|||

"ORDER BY 4" apparently does not work with windowed functions. This is the error message i got:

Msg 5308, Level 16, State 1, Line 1

Windowed functions do not support integer indices as ORDER BY clause expressions.

|||

jpazgier:

Maybe you can try something like this with another inner query?

WITH ProjectListAS
(
Select RR.*,
ROW_NUMBER()OVER (ORDER BY NumberOfUsers)AS'RowNumber'
from (SELECT
Id,
Name,
Created,
(SELECTCOUNT(*)FROM UserProjectsWHERE ProjectId = p.Id)AS NumberOfUsers,
FROM Projects p) RR
)
SELECT *
FROM ProjectList
WHERE RowNumberBETWEEN 50AND 60;

Works like a charm! Thank you :)