I have a table like this:
-----+--------+--------+-----+-----+-----+-----+-----+-----+-----+-----+------------
r_id | r_user| module | q_1 | q_2 | q_3 | q_4 | q_5 | q_6 | q_7 | q_8 | q_9
-----+--------+--------+-----+-----+-----+-----+-----+-----+-----+-----+------------
1 | test | 1 | g | r | r | y | g | g | y | r | g
-----+--------+--------+-----+-----+-----+-----+-----+-----+-----+-----+------------
2 | test-2| 1 | r | r | g | r | r | y | y | r | g
Is there any way to determine the maximum common value for each row? For example, the first row should result "g" and the second row should result "r".
I can't figure it out how to do that in MYSQL. Any help on that?
It appears you are working with fixed values or "r", "g" and "y" values. If so, this should work for you. The inner "PQ" (pre-Query) is getting each row other data elements and then summing each column with 1 IF it's value is the "r", "g" or "y" respectively into 3 count columns.
From that, I'm then using the "Greatest" function which returns the highest value from a given list provided. If you have all 3 with 3 of same color, they will all be flagged as highest... or even if a 4/4/1 split, two will show as high count.
I didn't know your table name so I just called it "freq1"
select
PQ.r_id,
PQ.r_user,
PQ.module,
PQ.gCnt,
PQ.rCnt,
PQ.yCnt,
if( PQ.gCnt = greatest( PQ.gCnt, PQ.rCnt, PQ.yCnt ), 'yes', ' ' ) as HighG,
if( PQ.rCnt = greatest( PQ.gCnt, PQ.rCnt, PQ.yCnt ), 'yes', ' ' ) as HighR,
if( PQ.yCnt = greatest( PQ.gCnt, PQ.rCnt, PQ.yCnt ), 'yes', ' ' ) as HighY,
greatest( PQ.gCnt, PQ.rCnt, PQ.yCnt ) as HighCnt
from
( select
r_id, r_user, module,
if( q_1 = 'g', 1, 0 )
+ if( q_2 = 'g', 1, 0 )
+ if( q_3 = 'g', 1, 0 )
+ if( q_4 = 'g', 1, 0 )
+ if( q_5 = 'g', 1, 0 )
+ if( q_6 = 'g', 1, 0 )
+ if( q_7 = 'g', 1, 0 )
+ if( q_8 = 'g', 1, 0 )
+ if( q_9 = 'g', 1, 0 ) as gCnt,
if( q_1 = 'r', 1, 0 )
+ if( q_2 = 'r', 1, 0 )
+ if( q_3 = 'r', 1, 0 )
+ if( q_4 = 'r', 1, 0 )
+ if( q_5 = 'r', 1, 0 )
+ if( q_6 = 'r', 1, 0 )
+ if( q_7 = 'r', 1, 0 )
+ if( q_8 = 'r', 1, 0 )
+ if( q_9 = 'r', 1, 0 ) as rCnt,
if( q_1 = 'y', 1, 0 )
+ if( q_2 = 'y', 1, 0 )
+ if( q_3 = 'y', 1, 0 )
+ if( q_4 = 'y', 1, 0 )
+ if( q_5 = 'y', 1, 0 )
+ if( q_6 = 'y', 1, 0 )
+ if( q_7 = 'y', 1, 0 )
+ if( q_8 = 'y', 1, 0 )
+ if( q_9 = 'y', 1, 0 ) as yCnt
from freq1 ) PQ
results created
r_id r_user module gCnt rCnt yCnt HighG HighR HighY HighCnt
1 test 1 4 3 2 yes 4
2 test-2 1 2 5 2 yes 5
I don't know of a way to do what you want in MySQL without resorting to many queries and perhaps temporary tables. You could of course much more easily use a single query and do the sorting in PHP with arrays.
Anyway the very organization of your table is wrong for that kind of purpose. You should have a related table where you store all the answers (or whatever q_1 to q_9 is). That way you can add any number of questions/answers without having to create columns in your main table.
Then MySQL would be the perfect tool to extract the result you want: you would be comparing rows instead of columns.