Filter Rank Based On Column Not being Ordered By

Question

Here is some fake/sample data:

DECLARE @tbl1 TABLE (PersonID VARCHAR(255), FirstNM VARCHAR(100), FileNBR VARCHAR(255));
INSERT INTO @tbl1 (PersonID, FirstNM, FileNBR) VALUES
('456789', 'NONAME', 'FileNBR780'),
('456789', 'John', 'FileNBR781'),
('456789', 'NONAME', 'FileNBR783'),
('476362', 'NONAME', 'FileNBR780'),
('476362', 'Mary', 'FileNBR781'),
('476362', 'Mary', 'FileNBR783')

PersonID  FirstNM   FileNBR
456789    NONAME    FileNBR780
456789    John      FileNBR781
456789    NONAME    FileNBR783
476362    NONAME    FileNBR780
476362    Mary      FileNBR781
476362    Mary      FileNBR783

I am trying to rank off of FileNBR and PersonID. I'd like to make any PersonID that has a FirstNM of "NONAME" to be the last ranked, despite the ranking in the FileNM.

Here is what I have so far. How do you achieve NONAME not being ranked first?

SELECT
 PersonID
 ,FirstNM  
 ,ROW_NUMBER() OVER(PARTITION BY PersonID ORDER BY FileNBR ASC) AS RankNBR
FROM @tbl1

Which yields these results:

PersonID    FirstNM  RankNBR
456789      NONAME   1
456789      John     2
456789      NONAME   3
476362      NONAME   1
476362      Mary     2
476362      Mary     3 

But, these are the desired Results:

PersonID    FirstNM  RankNBR
456789      John     1
456789      NONAME   2
456789      NONAME   3
476362      Mary     1
476362      Mary     2
476362      NONAME   3

Answer 1

You're almost there, you just need another ordering condition to rank your nonames before the RankNBR

SELECT
 PersonID
 ,FirstNM  
 ,ROW_NUMBER() OVER(PARTITION BY PersonID ORDER BY case when firstNM='NONAME' then 1 else 0 end,FileNBR ASC) AS RankNBR
FROM @tbl1