# Compare possible null values in one query

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?

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.

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)
``````
• Problem is that `NodeId` can contain any `int` value thus I cannot cast it to int, it could be a valid value. Zero, Minus one and so on are valid. – Alex Zhukovskiy May 12 '16 at 11:25