How to Treat Max() of an Empty Table as 0 Instead of Null

How to treat MAX() of an empty table as 0 instead of NULL

Just use Coalesce or NVL to handle NULLs.

The following code will return 0 if MAX(cid) is NULL

SELECT COALESCE(MAX(cid), 0)
FROM   itemconfiguration

SELECT MAX(id) in an empty table returns NULL instead of 0

Use COALESCE to replace NULL expression with something else:

SELECT COALESCE(MAX(id), 0) + 1 FROM table

I would suggest using an AUTO_NUMBER field instead of generating the id.

How set 0 with MAX function when it is NULL?

Well, as there is no date like 2014, you would expect null, because the maximum of nothing is actually not anyting.

But do this:

COALESCE(MAX(number),0)

Which means: get the first non-null thing from the next list, so if your max is null, it'll give you 0

How can I include null values in a MIN or MAX?

It's a bit ugly but because the NULLs have a special meaning to you, this is the cleanest way I can think to do it:

SELECT recordid, MIN(startdate),
   CASE WHEN MAX(CASE WHEN enddate IS NULL THEN 1 ELSE 0 END) = 0
        THEN MAX(enddate)
   END
FROM tmp GROUP BY recordid

That is, if any row has a NULL, we want to force that to be the answer. Only if no rows contain a NULL should we return the MIN (or MAX).

SELECT max(x) is returning null; how can I make it return 0?

In SQL 2005 / 2008:

SELECT ISNULL(MAX(X), 0) AS MaxX
FROM tbl WHERE XID = 1

Correct option for handle MAX() when table is empty?

IMO the working third option is the best. Database queries return null for most of the aggregate functions (except COUNT) when the set is empty, so is the suggestion by the exception message.

All you need is to include a cast to the corresponding nullable type:

LastInsertedID = db.myTable.Max(p => (long?)p.id);

How to replace (null) values with 0 output in PIVOT

SELECT CLASS,
isnull([AZ],0),
isnull([CA],0),
isnull([TX],0)
FROM #TEMP
PIVOT (SUM(DATA)
FOR STATE IN ([AZ], [CA], [TX])) AS PVT
ORDER BY CLASS

How consider NULL as the MAX date instead of ignoring it in MySQL?

Give this a shot:

SELECT ID, case when MAX(DATE IS NULL) = 0 THEN max(DATE) END AS DATE
FROM test
GROUP BY ID;

Entity Framework calling MAX on null on Records

Yes, casting to Nullable of T is the recommended way to deal with the problem in LINQ to Entities queries. Having a MaxOrDefault() method that has the right signature sounds like an interesting idea, but you would simply need an additional version for each method that presents this issue, which wouldn't scale very well.

This is one of many mismatches between how things work in the CLR and how they actually work on a database server. The Max() method’s signature has been defined this way because the result type is expected to be exactly the same as the input type on the CLR. But on a database server the result can be null. For that reason, you need to cast the input (although depending on how you write your query it might be enough to cast the output) to a Nullable of T.

Here is a solution that looks slightly simpler than what you have above:

var version = ctx.Entries 
    .Where(e => e.Competition.CompetitionId == storeCompetition.CompetitionId) 
    .Max(e =>(int?)e.Version);

Hope this helps.

Max return value if empty query

int maxShoeSize = Workers.Where(x => x.CompanyId == 8)
                         .Select(x => x.ShoeSize)
                         .DefaultIfEmpty(0)
                         .Max();

The zero in DefaultIfEmpty is not necessary.

---

Related Topics