Title: SELECT MAX RANK INCLUDING NULLS
Tags: sql;tsql;max;dense-rank
Question: I'm trying to select the MAX ranking from the rows returned whilst at the same time including the row/s which contains NULL values in the Award Date field. How would I go about this ?
SELECT
V.Name
,Selected
,SRV.SBID
,SRV.DateCreated
,SRV.Version
,[Awarded] = ARW.DateCreated
,[Rank] = CASE WHEN ARW.DateCreated IS NOT NULL THEN DENSE_RANK() OVER (PARTITION BY SRV.SBID ORDER BY ARW.DateCreated ) ELSE 0 END
FROM SRV
LEFT JOIN ARW ON ARW = SRV.ID
LEFT JOIN V   ON V.ID = SRV.VALUE
WHERE SRV.SBID = 767
CURRENT OUTPUT:
Name   Selected ID  Date                   Num  Award Date             Rank
AM Demo     1   767 2020-01-09 14:39:25.180 3   NULL                    0
Ven Two     1   767 2020-01-09 14:39:26.383 3   2020-01-09 14:42:23.677 2
Ven Two     1   767 2020-01-09 14:39:26.383 3   2020-01-15 14:41:05.680 3
REQUIRED OUTPUT:
Name   Selected ID  Date                   Num  Award Date             Rank
AM Demo     1   767 2020-01-09 14:39:25.180 3   NULL                    0
Ven Two     1   767 2020-01-09 14:39:26.383 3   2020-01-15 14:41:05.680 3
Comment: @MattDouhan sorry I meant include, i've edited the question

Comment: You state you want to exclude rows that contains null but your required output still has a row with null in it am I missing something here??

Here is the accepted answer: Add one more field in your query:
,[Rank2] = CASE
WHEN ARW.DateCreated IS NOT NULL
THEN DENSE_RANK() OVER (PARTITION BY SRV.SBID
ORDER BY ARW.DateCreated DESC)
ELSE 0
END
Then, in an outer query, select row with Rank2 IN (0,1)
Comment for this answer: Awarded this one as its more efficient and requires less code

Here is another answer: You could use a row number to get rid of the duplicates
with mydata as
*your query here*
rankgen as
select mydata.*, row_number() over(partition by Name order by rank desc) rn
from mydata
select *
from rankgen
where rn = 1
Comment for this answer: @GiorgosBetsos It doesn't exclude `NULL` values, I've tested it
Comment for this answer: Isn't this going to exclude the `NULL` values?

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 2, 'CommentCount': 2, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2020:00:01/15/20', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<sql><tsql><max><dense-rank>', 'Title': 'SELECT MAX RANK INCLUDING NULLS', 'ViewCount': 129, 'id': 59754679, 'user_AboutMe': '', 'user_AccountId': 4141046.0, 'user_DisplayName': 'jcoke', 'user_Reputation': 1331.0, 'user_Views': 139.0, 'user_id': 3396351.0}}