T-sql – Compare possible null values in one query

nullt-sql

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?

Best Answer

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.

Related Question