Row_number() returns null value how to solve this unexpected problem

Question

Here the SQL query

;WITH rankedresults AS 
(
    SELECT 
        userid, 
        rowid 
    FROM   
        (SELECT 
             userid, 
             ROW_NUMBER() OVER (ORDER BY referredplayercount DESC) AS RowId 
         FROM   
             tblusersprofile) dt 
    WHERE  
         rowid BETWEEN 1 AND 50
) 
SELECT 
    tblusersprofile.userid, 
    referredplayercount, 
    username, 
    avatarimagelink, 
    authoritylevel, 
    rowid 
FROM
    tblusersprofile 
LEFT JOIN 
    rankedresults ON tblusersprofile.userid = rankedresults.userid 
WHERE  
    tblusersprofile.userid IN (SELECT rankedresults.userid 
                               FROM rankedresults) 
    AND referredplayercount > 0 
ORDER BY 
    rowid ASC 

Here the returned results which is not expected to have null row number

I wonder that is rankedresults re-generated with this query again?

SELECT rankedresults.userid 
FROM rankedresults

Answer 1

Userid is 1137 is only in the tbluserprofile table, and not in the rankedresults CTE.

Because it's a LEFT JOIN, since there's no match, the rowid in the result shows as NULL.

The row_number() function itself can't generate NULL's.

But to make sure that the ROW_NUMBER in the CTE always returns the exact same order, add the userid in the ORDER BY

ROW_NUMBER() OVER (ORDER BY referredplayercount DESC, userid) AS RowId

But perhaps you could avoid using that CTE twice?

;WITH rankedresults AS 
(
    SELECT 
     userid, 
     RowId
    FROM
    (
       SELECT 
        userid, 
        ROW_NUMBER() OVER (ORDER BY referredplayercount DESC, userid ASC) AS RowId 
       FROM tblusersprofile
       WHERE referredplayercount > 0 
    ) dt
    WHERE RowId BETWEEN 1 AND 50
) 
SELECT 
    usrprof.userid, 
    usrprof.referredplayercount, 
    usrprof.username, 
    usrprof.avatarimagelink, 
    usrprof.authoritylevel, 
    rr.rowid 
FROM
    tblusersprofile usrprof
JOIN 
    rankedresults rr ON rr.userid = usrprof.userid
ORDER BY 
    rr.RowId ASC

And if you can use OFFSET & FETCH, and you don't really need that RowId in the select?
Then you might not even need a CTE with a row_number to page the results.

declare @offsetrows int = 0;
declare @nextrows int = 50;

SELECT 
 userid, 
 referredplayercount, 
 username, 
 avatarimagelink, 
 authoritylevel
FROM tblusersprofile
WHERE referredplayercount > 0
ORDER BY referredplayercount DESC, userid ASC
OFFSET @offsetrows ROWS FETCH NEXT @nextrows ROWS ONLY;

Simplified tests on db<>fiddle here