Is it possible to know the Column selected by GREATEST in Mysql?

Asked

Viewed 23 times

0

Hypothetically, having 8 columns in an X table, 4 of these columns store an average:

AVG(colunaN)

And the other four store the amount of elements of that average

COUNT(column)

In a query I’m trying to get the highest average of these 4, so I use the function:

GREATEST(coluna1,coluna2,coluna3,coluna4) AS maior_media

How can I do to identify which column was selected as the largest in "GREATEST" to return your 

COUNT(colunaSelecionadaPorGreatest)

Which in the above situation is stored in one of the other 4 columns.

mysql sql

  • Wouldn’t it be more practical to work with rows and not columns? Then just a GROUP BY/MAX.

  • well, in the architecture of this college project of mine, I defined that it is better that I update the averages and counts in a separate table, to avoid making these calculations for each item I will show in the system, so it updates this data periodically using cronjobs.

  • Then do a gambit using CASE / WHEN.

1 answer

0

It was very simple to solve, I found the use of variables in Mysql, so I stored the result of "GREATEST" in a:

SELECT 
    @greatest := (GREATEST(coluna1,coluna2,coluna3,coluna4)) AS greatest_average,
    CASE @greatest
         WHEN coluna1 THEN count_coluna1
         WHEN coluna2 THEN count_coluna2
         WHEN coluna3 THEN count_coluna3
         WHEN coluna4 THEN count_coluna4
    END AS greatest_counter,
    CASE @greatest
         WHEN coluna1 THEN "Qualidade"
         WHEN coluna2 THEN "Velocidade"
         WHEN coluna3 THEN "Durabilidade"
         WHEN coluna4 THEN "Quantidade"
    END AS greatest_counter_type

And I created two CASES, one to store the value of the counter corresponding to the column selected by "GREATEST", and the other to store the selected type.

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.