Skip to content
Advertisement

min of substring across many rows- exclude blank and take the least value

I have a sample data here: What I need is for each product_id, the least number is each level

Expected result

= 10093    3    183   184   185

But as I have multiple rows for the same product id, it selects blank over 184 in level 4

Click here for example dataset- Product in different levels

select 
cc.product_id,
substring_index(substring_index(concat(cc.path, '/////'), '/', 2), '/', -1) as level_2,   
substring_index(substring_index(concat(cc.path, '/////'), '/', 3), '/', -1) as level_3,   
substring_index(substring_index(concat(cc.path, '/////'), '/', 4), '/', -1) as level_4,
substring_index(substring_index(concat(cc.path, '/////'), '/', 5), '/', -1) as level_5

from 
cc

Advertisement

Answer

You can use case when you aggregate to filter out the 0 values:

select product_id,
       min(case when level_2 + 0 > 0 then level_2 + 0 end) as min_level_2,
       min(case when level_3 + 0 > 0 then level_3 + 0 end) as min_level_3,
       min(case when level_4 + 0 > 0 then level_4 + 0 end) as min_level_4,
       min(case when level_5 + 0 > 0 then level_5 + 0 end) as min_level_5
from (select cc.product_id,
             substring_index(substring_index(concat(cc.path, '/////'), '/', 2), '/', -1) as level_2,   
             substring_index(substring_index(concat(cc.path, '/////'), '/', 3), '/', -1) as level_3,   
             substring_index(substring_index(concat(cc.path, '/////'), '/', 4), '/', -1) as level_4,
             substring_index(substring_index(concat(cc.path, '/////'), '/', 5), '/', -1) as level_5
      from cc
     ) cc
group by product_id;

Note: You could also write the condition as when level_2 <> ''.

6 People found this is helpful
Advertisement