passing a scalar query result to coalesce

Question

How can I pass the result from a scalar [single row, single value] query to coalesce? I am trying to pick the priority as (the biggest priority so far in the table) + 1. [0 if it is the first row.]

create trigger priority_SuperRuleSamples before insert on SuperRuleSamples 
FOR EACH ROW 
  SET NEW.Priority=coalesce(NEW.Priority, 
   coalesce(
   select Priority from SuperRuleSamples order by Priority desc limit 1, 
   -1
   )+1
  )

Answer 1

it seems there is a parenthesis issue in your query. Can you try this?

create trigger priority_SuperRuleSamples before insert on SuperRuleSamples 
FOR EACH ROW 
  SET NEW.Priority=coalesce(NEW.Priority, 
   coalesce(
    (select Priority from SuperRuleSamples order by Priority desc limit 1), 
   -1
   )+1
  )