It is necessary to choose the minimum value from the field, but if it turns out that it is equal to 0, then you need to take the following value ... Ie if a simple sample returns for example the numbers 13, 34, 5, 0, 89 - I need to get 5, and if a simple sample returns all rows in which only zeros, then I need to return 0 ...

thank

  • and to write a condition where val > 0 problematic? - teran
  • DBMS dialect specify more in tags - teran

2 answers 2

 COALESCE(MIN(CASE WHEN val != 0 THEN val END), 0)
  • If instead of zero, in the absence of others, it suits Null, then COALESCE can be removed. - Akina
 SELECT IF(t.min_res = 0 AND t.max_res = 0, 0, t.res) FROM (SELECT MIN(a.value) AS min_res, MAX(a.value) AS max_res, MIN(b.value) AS res FROM `table` a LEFT JOIN (SELECT * FROM `table` WHERE `value` <> 0) AS b ON b.`value` ) t;

value here is the name of your column, table is the name of your table