-1

How to retrieve the column name which has maximum value by comparing the values from multiple columns using 'Case' statements

For eg : there are more than 10 columns . 

  A B C D E       Result  
  1 2 3 1 5        E

column E has the highest no and in the result column i should get 'E' as the answer.

Here A B C D E Result are columns .. By comparing the values from A,B,C,D,E i should get the column name which has maximum no in the result column using Case expression.

Gaya3
  • 3
  • 3

3 Answers3

1

Most dialects of SQL support greatest() and least():

select greatest(a, b, c, d, e) as greatest,
       least(a, b, c, d, e) as least

Those that don't typically have some other mechanism. For instance, SQLite overloads min() and max():

select min(a, b, c, d, e) as greatest,
       max(a, b, c, d, e) as least

Or, in SQL Server, I would use apply:

select v.*
from t cross apply
     (select max(val) as greatest, min(val) as least
      from (values (a), (b), (c), (d), (e)) v(val)
     ) v;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

With CASE? Something like this, perhaps? 

SQL> with test (a, b, c, d) as
  2    (select 1, 2, 3, 1 from dual)
  3  select
  4    case when a >= b and a >= c and a >= d then a
  5         when b >= a and b >= c and b >= d then b
  6         when c >= a and c >= b and c >= d then c
  7         when d >= a and d >= b and d >= c then d
  8    end result
  9  from test;

    RESULT
----------
         3

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • It is right and simple when we have only fewer columns .But there are totally 15 columns . So I need simplest way to get the same result. – Gaya3 Jun 12 '18 at 22:30
  • Well, the simplest way would be one of those provided by Gordon. However, you said that it *must* be CASE, so - that's how it looks like. You'll have to do a lot of typing and do it carefully, otherwise you'll get the wrong result. – Littlefoot Jun 13 '18 at 19:21
-1

Can't believe that... Typical design issue. A lot of headaches come from the fact that database designers who are not supposed to touch a database at all.

You want get the max value from different column : meaning that the data inside those columns have the same context. We are here comparing apple, orange, peach and bananas !

Since you are comparing data which have the same context, they should all have been in the same column. Enabling people trying to help you avoiding to scratch their brain. pfff...

My solution is kinda barbaric...

create table jeez(
  jeez_id serial primary key,
  row_id integer,
  row_value integer)


insert into jeez (row_id, row_value)
select TheTableWithNoName_id, column_a from TheTableWithNoName
union
select TheTableWithNoName_id, column_b from TheTableWithNoName
union
select TheTableWithNoName_id, column_c from TheTableWithNoName
union
select TheTableWithNoName_id, column_d from TheTableWithNoName

So now you can do :

select row_id, max(row_value)
from jeez
group by 1
order by 1

Unbelievable.

olleo
  • 378
  • 3
  • 14