I have a subquery in a very large query that has to do the following
with an array of couples of clusters
array(array(c1, c2), array(c3, c4), array(c5, c6), array(c7, c8))
where for example c1 and c2 are complementary, c3 and c4 as well ..etc . and i have a table state :
id_state cluster successfull failed success_ratio
1 c1 4 0 100%
2 c2 1 9 10%
3 c3 0 4 0%
4 c4 1 1 50%
note that which cluster is coupled with the another is determined using the array above.
and the final output that i would like to have :
cluster successfull success_ratio
c1 4 100% (for the first pair)
c4 1 50% (for the second)
is there a way to do a query that gets the success_ratio of all the data by taking only from each couple the one with the success_ratio > 50% and only if both have success_ratio < 50% then just take the first one.
Is this even achievable using only a mysql query (i can't use query result since i want it as a subquery of another large query) ?
even if you can just suggest a starting point for a way to do it that would be appreciated.
It sounds like you just want the maximum success ratio for each pair.
select s.grp, max(success_ratio)
from state s join
(select 'c1' as cluster, 1 as grp union all
select 'c2', 1 union all
select 'c3', 2 union all
select 'c4', 2 union all
select 'c5', 3 union all
select 'c6', 3 union all
select 'c7', 4 union all
select 'c8', 4
) grps
on s.cluster = grps.cluster
group by s.grp;
If you actually want the rows with the best success, then use a subquery:
select s.*
from (select s.grp,
substring_index(group_concat(cluster order by success_ratio desc), ',', 1) as bestcluster
from state s join
(select 'c1' as cluster, 1 as grp union all
select 'c2', 1 union all
select 'c3', 2 union all
select 'c4', 2 union all
select 'c5', 3 union all
select 'c6', 3 union all
select 'c7', 4 union all
select 'c8', 4
) grps
on s.cluster = grps.cluster
group by s.grp
) b join
state s
on s.cluster = b.cluster