Sorting in CTE expression

Question

Hi guys,

I am retreiving all users from DB ordered by number of followers for each user DESC

 with TH_Users as
(
    SELECT [ID]
      ,[FullName]
      ,[UserName]
      ,[ImageName]
      ,dbo.GetUserFollowers(ID) AS Followers
, ROW_NUMBER() OVER (order by ID ) AS 'RowNumber' 
from dbo.TH_Users
Where CultureID = @cultureID

)
Select ID,[FullName]
      ,[UserName]
      ,[ImageName], Followers from TH_Users
Where RowNumber BETWEEN @startIdx AND @endIdx
Order BY Followers DESC

I am using a function to get number of followers for each user. now is I user Followers column as the column order for ROW_NUMBER() OVER (order by Followers ) AS 'RowNumber'

I get a compilation error.

Putting Order BY Followers DESC at the end of the query will not give the intended result.

Any suggestions ?

Thanks

Answer 1

When you use AS to give an alias to a column, that alias is not available within the query - logically, applying aliases to columns is (almost) the very last part of evaluating a query.

So if you want your ROW_NUMBER with the CTE to be OVER what you alias as Followers, you must express it in the same terms as the column itself:

;with TH_Users as
(
    SELECT [ID]
      ,[FullName]
      ,[UserName]
      ,[ImageName]
      ,dbo.GetUserFollowers(ID) AS Followers
, ROW_NUMBER() OVER (order by dbo.GetUserFollowers(ID) ) AS 'RowNumber' 
from dbo.TH_Users
Where CultureID = @cultureID
)

Note that this will not cause the function to be evaluated any more times than it is currently.

(I have not tested this)