Compare possible null values in one query

Question

Question is pretty short and simple: I have following query:

SELECT *
FROM CTE

Now I want to get all rows with only maximum value of some field.

SELECT *
FROM CTE
WHERE NodeId = (SELECT MAX(NodeId) FROM CTE)

But maximum value can be null, so this query doesn't work, and I should write:

SELECT *
FROM CTE
WHERE NodeId = (SELECT MAX(NodeId) FROM CTE) 
OR (SELECT MAX(NodeId) FROM CTE) is null

So I should write another OR do return true if current value is equal to maximum or maximum is null (we shouldn't check for NodeId is null because it defenitly is).

Is there any way to simplify this script?

Answer 1

Your 2nd query seems correct and does what you want.

Another option would be to use the TOP .. WITH TIES but this works only because NULL values are treated as the lowest in SQL Server.

SELECT TOP (1) WITH TIES t.*
FROM cte AS t
ORDER BY t.NodeId DESC ;

If you had MIN() instead of MAX() and you tried the TIES with ASC instead of DESC, the above would fail miserably.

Answer 2

Assuming your NodeId contains INT values, use ISNULL to replace all nulls with the least of int values. This way you'll include NULL values as your least probability to occur. Following is an example:

DECLARE @MinNodeId INT;
SET @MinNodeId = MIN(NodeId) - 1
FROM CTE;

SELECT *
FROM CTE
WHERE ISNULL(NodeId, @MinNodeId) = (SELECT MAX(ISNULL(NodeId, @MinNodeId)) FROM CTE)