Avoid Null Result of SELECT MAX Rank from Empty Table

You can use COALESCE() along with aggregate function MAX() for this.

The syntax is as follows

SELECT COALESCE(MAX(`yourColumnName`), 0) FROM yourTableName;

To understand the above syntax, let us create a table. The query to create a table is as follows

mysql> create table avoidNullDemo
   -> (
   -> `rank` int
   -> );
Query OK, 0 rows affected (0.53 sec)

Insert some records in the table using insert command.

The query is as follows

mysql> insert into avoidNullDemo values(10);
Query OK, 1 row affected (0.20 sec)
mysql> insert into avoidNullDemo values(NULL);
Query OK, 1 row affected (0.18 sec)
mysql> insert into avoidNullDemo values(20);
Query OK, 1 row affected (0.17 sec)
mysql> insert into avoidNullDemo values(NULL);
Query OK, 1 row affected (0.11 sec)
mysql> insert into avoidNullDemo values(100);
Query OK, 1 row affected (0.11 sec)
mysql> insert into avoidNullDemo values(NULL);
Query OK, 1 row affected (0.16 sec)

Display you all records from the table using select statement.

The query is as follows

mysql> select *from avoidNullDemo;

The following is the output with NULL values

+------+
| rank |
+------+
| 10   |
| NULL |
| 20   |
| NULL |
| 100  |
| NULL |
+------+
6 rows in set (0.00 sec)

Here is the query to avoid null result

mysql> select COALESCE(MAX(`rank`), 0) FROM avoidNullDemo;

The following is the output

+--------------------------+
| COALESCE(MAX(`rank`), 0) |
+--------------------------+
| 100                      |
+--------------------------+
1 row in set (0.00 sec)

Here is the case when table is empty.

Let us delete all records from the above table.

The query is as follows

mysql> truncate table avoidNullDemo;
Query OK, 0 rows affected (0.92 sec)

Now the above table is empty. Let us implement the above query for empty table

mysql> select COALESCE(MAX(`rank`), 0) FROM avoidNullDemo;

The following is the output

+--------------------------+
| COALESCE(MAX(`rank`), 0) |
+--------------------------+
| 0                        |
+--------------------------+
1 row in set (0.00 sec)