SQLite Room request, get value if found, or MAX(value)+1 if not

Question

Suppose I have the following table:

value	label
1	art2
2	art1
3	art4
4	art3

I would like to have the result:

• value field if label found
• MAX(value)+1 if not found

label = art4 => 3
label = non existing => MAX(value)+1 = 5

Answer 1

You can do it with conditional aggregation:

SELECT COALESCE(
         MAX(CASE WHEN label = ? THEN value END), 
         MAX(VALUE) + 1,
         1
       ) AS value
FROM tablename;

This query will also return 1 if the table is empty.
Change ? to the label that you want.

See the demo.