How to avoid null result of "SELECT max(rank) FROM test" for an empty table?

Question

I need to form an query in JPQL (2.0) to select a the max value (rank). I use: `SELECT max(rank) FROM test` This works fine, except the case where the table is empty, then the result is `null`, but I need `0`. This would be no problem if I was able to "catch" the null with an simple if-statement, but I can't do this (the framework allows only to specify an JPA Query but no java code). Does anybody have an idea how to tune that query to get an `0` instead of an `null` if the table is empty? - The database is an MySQL, and native queries or stored procedures are no option.

Accepted Answer

Maybe:

```
SELECT COALESCE(MAX(rank), 0) FROM test
```

EDIT

`COALESCE` seems supported by JPQL (JPA 2.0): http://java.sun.com/developer/technicalArticles/JavaEE/JavaEE6Overview_Part3.html

How to avoid null result of "SELECT max(rank) FROM test" for an empty table?

Solution

Problem