sql server row_number

0

Please login or register to vote for this query.

(click on this box to dismiss)

http://stackoverflow.com/questions/12881648/sql-server-row-number

Physics

Q&A for active researchers, academics and students of physics

DECLARE @question TABLE(id int, title VARCHAR(20));
DECLARE @customerLikeQuestion TABLE(id int, QuestionId int, CustomerId int);

INSERT INTO @question VALUES
(1, 'A'),
(1, 'C'),
(2, 'B');
INSERT INTO @customerLikeQuestion VALUES
(1, 1, 1),
(2, 1, 2),
(3, 2, 3),
(4, 3, 4),
(5, 1, 5),
(6, 1, 12),
(7, 1, 10),
(8, 2, 11),
(9, 3, 15);

;WITH LiksCounts
AS
(
    SELECT Id,
           (SELECT     COUNT(*) AS Expr1
            FROM          @customerLikeQuestion
            WHERE      (QuestionId = q.Id)) AS LikeCount
    FROM         @question AS q
    GROUP BY Id
)
SELECT *, row_number() over (order by likecount) as RowNum
fROM LiksCounts
ORDER BY likecount ASC



/*
 id    LikeCount
2136    6
2138    5
2150    5
*/

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results