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

From: Ninja Li <nickli2000_at_gmail.com>
Date: Fri, 18 Sep 2009 04:44:18 -0700 (PDT)
Message-ID: <785b2c3f-e12c-473e-ae63-ea5dd939703b_at_j9g2000vbp.googlegroups.com>

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 Received on Fri Sep 18 2009 - 06:44:18 CDT