Re: get the column_name of the maximum value for a row
Date: Fri, 18 Sep 2009 06:36:50 -0700 (PDT)
Message-ID: <0f0fbfd2-dc06-4319-b986-e7e03755ad14_at_g23g2000vbr.googlegroups.com>
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
> > |
> > |
> > |
>
> > Use DECODE.
> > What should be the value if 2 or more columns have the highest value?
>
> > Regards
> > Michel
>
> Thanks Michel. If two or more column have the highest value, the
> column name withe lowest value will be picked. For example, value_2
> column will be selected over value_3.
>
> Could you also elaborate on how to use the DECODE?
>
> Nick- Hide quoted text -
>
> - Show quoted text -
I was going to say use a CASE statement in your SELECT statement but
decode is basically the same functionality. when colA > colB and colA
> colC then 'colA '||value_a
You can find the full syntax of the CASE and DECODE in the SQL Manual.
Decode with examples
http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/functions33a.htm#SQLRF00631
CASE statement with example
http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/expressions5a.htm#1033394
If you did not need to know the column you could use the GREATEST function.
> set echo on
> select * from marktest4;
FLD1 FLD2 FLD3
---------- ---------- ----------
7 8 9
9 8 7
> _at_t19
> select case when fld1 > fld2 and fld1 > fld3 then 'FLD1 '||fld1
2 when fld2 > fld1 and fld2 > fld3 then 'FLD2 '||fld2 3 when fld3 > fld1 and fld3 > fld2 then 'FLD3 '||fld3 4 else 'Error' end "VALUE"5 from marktest4
6 /
VALUE
FLD3 9
FLD1 9 HTH -- Mark D Powell -- Received on Fri Sep 18 2009 - 08:36:50 CDT