Checking SQL MAX() Function returning null

问题:

My problem is if my table is empty or the column on which I am using max function does not have the value I have specified, then why is sqlDataReader.hasRows TRUE?

It is giving a null record...how do I solve this problem??

Thanks in advance.

回答1:

An aggregate function like MAX() will always return one row per group. In your case, your group is the whole table. Therefore you get a one row resultset with the MAX value. Since you don't have data in your table, the MAX value is undefined, therefore NULL.

For better understanding try to replace MAX with COUNT. This will also return one row but with value 0. I think this is more intuitive and will help you better to understand what is happening.

回答2:

Make your query into a derived table and filter out null values in the main query.

select T.MaxValue
from (
     -- Your query goes here
     select max(Value) as MaxValue
     from YourTable
     where SomeCol > 10
     ) as T
where T.MaxValue is not null

回答3:

you can use:

select top(select COUNT(*) from tbl) max(field) from tbl

回答4:

Or, use the HAVING clause because HAVING can be used with aggregate functions:

select max(Value) from YourTable where SomeCol > 10
HAVING max(Value) is not null