0

I have an unusual table structure which looks like this:

fruit   january   february   march
----------------------------------
apple   10        9          3
banana  2         1          1
grape   8         7          7

What type of select given 'apple' as the value of the row in the fruit column will give me the column and the highest number for that column, ie

apple, january, 10

haz
  • 740
  • 1
  • 11
  • 20
  • 4
    You need to unpivot your data, see https://stackoverflow.com/questions/15184381/mysql-turn-table-into-different-table – Vivek Jul 10 '19 at 08:10
  • 2
    What have you tried so far? Also, not to be nasty but the question should be improved a little, the title is much too generic IMO, you want to do more than just add a `where` clause, also, there are not really different "types" of select requests – Kaddath Jul 10 '19 at 08:10
  • 1
    Stop with all this silliness, and just have a usual table structure instead. – Strawberry Jul 10 '19 at 09:04
  • @ Strawberry - I agree, a bit awk-ing would do the trick – haz Jul 10 '19 at 09:23

1 Answers1

2

Your unusual table structure allows complicated and not so elegant solutions.
You can use the function greatest() to get the max value for 'apple' and use it in a CASE statement to get the month's name:

select
  fruit,
  case greatest(january, february, march)
    when january then 'january'
    when february then 'february'
    when march then 'march'
  end,
  greatest(january, february, march)
from tablename
where fruit = 'apple'

I hope you understand what you have to do if the table contains 12 columns for the 12 months.

forpas
  • 160,666
  • 10
  • 38
  • 76