Question: I have the following query in MySQL. This inserts a new item to a list where the position of the new item is set to 1 greater than the current maximum position.

```
INSERT INTO items
SET item_name="apple",
    position=(SELECT MAX(i.position)+1 FROM items i WHERE i.list_id=12),
    list_id=12

```

This works, but for `position`, I want to insert a 0 if the SELECT statement returns an empty set. Is this possible to do in one query (so that I can avoid using transactions)?

Answer: 
Try COALESCE():

```
INSERT INTO items
SET item_name="apple",
position=COALESCE((SELECT MAX(i.position)+1 FROM items i WHERE i.list_id=12), 0),
list_id=12

```
use `CASE` to check if you have any `position` at all by doing `IS NULL`. If you don't, just use 0. 

```
INSERT INTO items
SET item_name="apple",
    position=( CASE WHEN (SELECT MAX(i.position)+1 FROM items i WHERE i.list_id=12) IS NULL 
                THEN 0 ELSE (SELECT MAX(i.position)+1 FROM items i WHERE i.list_id=12) END ) ,
    list_id=12

```

See [demo](http://sqlfiddle.com/#!2/6fdfe/1) 

---

Though this works, see @criticalfix's better answer and a demo for both [here](http://sqlfiddle.com/#!2/44c48/1).