Item 1
SELECT
@RowIndex = RowID
FROM
CBT
WHERE
RowID = 1;
which actually means
SELECT @RowIndex = 1;
Item 2
UPDATE
#Test
SET
Ranking = NULL;
DECLARE
@ID INT,
@MemberID INT,
@PrevMemberID INT = -1,
@Ranking INT = 1;
WHILE 1 = 1
BEGIN
SELECT TOP 1
@ID = ID,
@MemberID = MemberID
FROM
#Test
WHERE
Ranking IS NULL
ORDER BY
MemberID,
Score DESC;
IF @@ROWCOUNT = 0
BREAK;
IF @PrevMemberID <> @MemberID
SET @Ranking
= 1;
UPDATE
#Test
SET
Ranking = @Ranking
WHERE
ID = @ID;
SET @PrevMemberID = @MemberID;
SET @Ranking = @Ranking
+ 1;
END;
WITH CTE AS (
SELECT
ID,
ROW_NUMBER() OVER (PARTITION BY MemberID ORDER BY Score DESC) AS Ranking
FROM
#Test)
UPDATE
t
SET
Ranking = t.Ranking
FROM
#Test t
INNER JOIN
CTE a
ON a.ID = t.ID;
SELECT
ReplyDelete@RowIndex = RowID
FROM
CBT
WHERE
RowID = 1;
Unless the table is empty and then it'd be NULL.
Agreed, however in the context of this code having an empty table did not change the behavior. The developer was literally trying to set @RowIndex to 1.
ReplyDelete