Re: get the column_name of the maximum value for a row

From: modu <bozgul_at_gmail.com>
Date: Thu, 5 Nov 2009 01:55:59 -0800 (PST)
Message-ID: <1714f4b5-88b1-41d0-a71b-87b0f8303a20_at_s15g2000yqs.googlegroups.com>

On Sep 18, 5:19 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "ddf" <orat..._at_msn.com> a écrit dans le message de news: 9adadbc9-22c9-4138-90e5-9f3647c28..._at_h30g2000vbr.googlegroups.com...
> On Sep 18, 8:36 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > On Sep 18, 7:44 am, Ninja Li <nickli2..._at_gmail.com> wrote:
>
> > > On Sep 18, 12:18 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
>
> > > > "Ninja Li" <nickli2..._at_gmail.com> a écrit dans le message de news:
> > > > 328aa22c-967d-4ac1-b453-8a73b3147..._at_33g2000vbe.googlegroups.com...
> > > > | Hi,
> > > > |
> > > > | I want to get the column_name of the maximum value of a row. To
> > > > | simplify, the table has the following format and values, with
> > > > | input_date being unique. The columns evaluated are the "value_"
> > > > | columns.
> > > > |
> > > > | input_date value_1 value_2 value_3 ** maximum
> > > > | value ** maximum value_column
> > > > | 01-SEP-09 10 15 8
> > > > | 15 value_1
> > > > | 02-SEP-09 12 5 18
> > > > | 18 value_3
> > > > | 03-SEP-09 9 12 12
> > > > | 12 value_2
> > > > | ...........
> > > > |
> > > > | The desired output is:
> > > > | 01-SEP-09 15 value_1
> > > > | 02-SEP-09 18 value_3
> > > > | 03-SEP-09 12 value_3
> > > > | ..........
> > > > |
> > > > | Is the problem solvable using plain SQL? Or do I need to use PL/
> > > > | SQL? The database is Oracle 9i.
> > > > |
> > > > | Thanks in advance.
> > > > |
> > > > | Nick
> > > > |
> > > > |
> > > > |

select greatest(val1,val2,val3) ,
case

when greatest(val1,val2,val3) = val1 then 'val1'
when greatest(val1,val2,val3) = val2 then 'val2'
when greatest(val1,val2,val3) = val3 then 'val3'

end
from table1 Received on Thu Nov 05 2009 - 03:55:59 CST