SQL Server row_number

Question

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

Community · Accepted Answer · 2021-01-18 12:30:34Z

3

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

Live DEMO

edited Jan 18, 2021 at 12:30

CommunityBot

11 silver badge

answered Oct 14, 2012 at 11:08

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nam vo Over a year ago

thanks for your prompt reply, but i need the result with rownum increasing 1 2 3 4 5, but still hold likecount top down like 6 5 3 1

Collectives™ on Stack Overflow

SQL Server row_number

1 Answer 1

Live DEMO

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related