Suppose I have the following table:

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

CodePudding user response：

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

• 1st row artx label not found so highest value (4) 1 = 5
• 2nd row art1 label found so value 2 returned
• 3rd row art2 label found so value 1 returned
• 4th row art3 label found so value 4 returned
• 5th row art4 label found so value 3 returned