user2511599 user2511599 -4 years ago 267
MySQL Question

MySql select min, max conditionally without subselect in Yii2

I have this kind of data in a table:

id a1 a2
01 _1 _1
01 _1 _2
01 _1 _3
01 _2 _1
01 _2 _2
01 _2 _3

is it possible to select min and max something like this:

select ... min(a2 where a1=1)

it is a part of a bigger joined set of tables and I don't know how to implement subselect in yii2 so I would like to find a way to avoid subselects, if possible. Can you please point me to the right direction? Many thanks!

Answer Source

a simple way is based on the use of CASE WHEN inside the aggregation function (min/max ..) eg

MIN( CASE WHEN a1 = 1 then a2 else a_proper_value END) 

where a_proper_value is the value that you want assing in the case a1<> 1

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download