S. F. S. F. - 7 months ago 9
SQL Question

mqsql - check if selected rows contain the same value

I need to check in mysql if certain columns contain the same value, but don't actually know the value yet. All the solutions I found until now were using count in combination with a where clause. But that doesn't work for me, because I don't know the values of the colums. For example:

Index ColB ColC ColD ColE
1 1 cat 1.3 black
2 1 cat 1.3 black
3 1 cat 1.3 white
4 1 cat 1.3 tiger
5 1 cat 1.3 white


I would like to check if the 3 columns ColB,ColC and ColD have the same value. For the table above it should return true. However for the following table it should return false

Index ColB ColC ColD ColE
1 1 dog 1.3 black
2 1 cat 1.3 black
3 2 cat 1.3 white
4 1 cat 1.3 tiger
5 1 cat 2.7 white


The rule should be if(ColB_hasDifferentValues || ColC_hasDifferentValues || ColD_hasDifferentValues) { return true } ;

Is that possible? As I said before, I don't know which animals are included in ColC, as users can insert new animals.

Thanks a lot in advance!

Answer

Just use max() and min():

select (case when max(b) = min(b) and max(c) = min(c) and max(d) = min(d)
             then 'same'
             else 'different'
        end)
from t;

This logic ignores NULL values (the OP does not mention NULL values at all). The idea can be extended, but the logic is a wee bit more complex.