Reputation: 3457
I have a problem to create a custom rownumber
with an alias column. Here are some sample data:
Table question
:
id title
-- --------
1 xx
2 xxx
..
Table customerLikeQuestion
:
Id QuestionId CustomerId
---------------------------
1 20 xx
2 100 xx
xx
Query:
SELECT q.Id,
(SELECT COUNT(*)
FROM dbo.CustomerLikeQuestion
WHERE (QuestionId = q.Id)) AS LikeCount
FROM dbo.Question AS q
ORDER BY likecount DESC
The previous query shows:
id LikeCount
2136 6
2138 5
2150 5
Now I'd like to put a rownumber
to count an incremental order of a row. I tried the following query:
SELECT TOP (100) PERCENT Id,
(SELECT COUNT(*) AS Expr1
FROM dbo.CustomerLikeQuestion
WHERE (QuestionId = q.Id)) AS LikeCount,
row_number() over (order by likecount) as RowNum
FROM dbo.Question AS q
ORDER BY likecount DESC
But it gives me the following error:
invalid column Likecount.
I do know that Over()
cannot work with alias, but how can I get around this problem, either with CTE or subquery, I have not yet come up with any idea. please help.
The right result should be like this:
id likecount, rownum
----------------------
xx 6 1
xx 5 2
xx 4 3
.. 0 xx
Upvotes: 0
Views: 11606
Reputation: 79979
Try this instead
;WITH LiksCounts
AS
(
SELECT Id,
(SELECT COUNT(*) AS Expr1
FROM dbo.CustomerLikeQuestion
WHERE (QuestionId = q.Id)) AS LikeCount
FROM dbo.Question AS q
GROUP BY Id
)
SELECT TOP(100) *, row_number() over (order by likecount) as RowNum
fROM LiksCounts
ORDER BY RowNum ASC
Upvotes: 3