Re: get the column_name of the maximum value for a row
From: Ninja Li <nickli2000_at_gmail.com>
Date: Fri, 18 Sep 2009 11:26:15 -0700 (PDT)
Message-ID: <2ed8ab20-7bd2-4a6f-9c7d-cd168f59af92_at_m20g2000vbp.googlegroups.com>
On Sep 18, 11:19 am, "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
> > > > |
> > > > |
> > > > |
>
> > > > 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 exampleshttp://download.oracle.com/docs/cd/B10501_01/server.920/a96540/functi...
>
> > CASE statement with examplehttp://download.oracle.com/docs/cd/B10501_01/server.920/a96540/expres...
>
> > 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 --- Hide quoted text -
>
> > - Show quoted text -
>
> For your example the code isn't 'pretty':
>
> SQL> create table value_test(
> 2 input_date date primary key,
> 3 value_1 number,
> 4 value_2 number,
> 5 value_3 number
> 6 );
>
> Table created.
>
> SQL>
> SQL> insert all
> 2 into value_test
> 3 values(to_date('01-SEP-09','DD-MON-RR'), 10, 15, 8)
> 4 into value_test
> 5 values(to_date('02-SEP-09','DD-MON-RR'), 12, 5, 18)
> 6 into value_test
> 7 values(to_date('03-SEP-09','DD-MON-RR'), 9, 12, 12)
> 8 into value_test
> 9 values(to_date('04-SEP-09','DD-MON-RR'), 19, 12, 12)
> 10 into value_test
> 11 values(to_date('05-SEP-09','DD-MON-RR'), 8, 1, 2)
> 12 into value_test
> 13 values(to_date('06-SEP-09','DD-MON-RR'), 9, 12, 4)
> 14 into value_test
> 15 values(to_date('07-SEP-09','DD-MON-RR'), 38, 42, 12)
> 16 into value_test
> 17 values(to_date('08-SEP-09','DD-MON-RR'), 89, 89, 89)
> 18 into value_test
> 19 values(to_date('09-SEP-09','DD-MON-RR'), 9, 17, 22)
> 20 into value_test
> 21 values(to_date('10-SEP-09','DD-MON-RR'), 12, 12, 17)
> 22 select * From dual;
>
> 10 rows created.
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> select input_date,
> 2 case when value_1 < value_2 and value_2 < value_3 then
> value_3
> 3 when value_3 < value_2 and value_2 < value_1 then
> value_1
> 4 when value_3 < value_1 and value_1 < value_2 then
> value_2
> 5 when value_3 < value_2 and value_2 = value_1 then
> value_1
> 6 when value_3 = value_2 and value_2 < value_1 then
> value_2
> 7 when value_3 > value_1 and value_1 > value_2 then
> value_3
> 8 when value_3 = value_2 and value_2 > value_1 then
> value_2
> 9 when value_1 > value_2 and value_2 < value_3 then
> value_1
> 10 when value_1 = value_2 and value_2 = value_3 then
> value_1
> 11 when value_1 = value_2 and value_2 < value_3 then
> value_3
> 12 end max_val,
> 13 case when value_1 < value_2 and value_2 < value_3 then
> 'value_3'
> 14 when value_3 < value_2 and value_2 < value_1 then
> 'value_1'
> 15 when value_3 < value_1 and value_1 < value_2 then
> 'value_2'
> 16 when value_3 < value_2 and value_2 = value_1 then
> 'value_1'
> 17 when value_3 = value_2 and value_2 < value_1 then
> 'value_2'
> 18 when value_3 > value_1 and value_1 > value_2 then
> 'value_3'
> 19 when value_3 = value_2 and value_2 > value_1 then
> 'value_2'
> 20 when value_1 > value_2 and value_2 < value_3 then
> 'value_1'
> 21 when value_1 = value_2 and value_2 = value_3 then
> 'value_1'
> 22 when value_1 = value_2 and value_2 < value_3 then
> 'value_3'
> 23 end max_val_col
> 24 from value_test;
>
> INPUT_DATE MAX_VAL MAX_VAL
> -------------------- ---------- -------
> 01-SEP-2009 00:00:00 15 value_2
> 02-SEP-2009 00:00:00 18 value_3
> 03-SEP-2009 00:00:00 12 value_2
> 04-SEP-2009 00:00:00 12 value_2
> 05-SEP-2009 00:00:00 8 value_1
> 06-SEP-2009 00:00:00 12 value_2
> 07-SEP-2009 00:00:00 42 value_2
> 08-SEP-2009 00:00:00 89 value_1
> 09-SEP-2009 00:00:00 22 value_3
> 10-SEP-2009 00:00:00 17 value_3
>
> 10 rows selected.
>
> SQL>
>
> but it does work.
>
> David Fitzjarrell
>
> ---------------------------------------
>
> What I had in mind is the following one:
>
> SQL> select input_date,
> 2 decode(greatest(value_1,value_2,value_3),
> 3 value_1,'VALUE_1',
> 4 value_2,'VALUE_2',
> 5 'VALUE_3') col,
> 6 greatest(value_1,value_2,value_3) value
> 7 from value_test
> 8 order by 1
> 9 /
> INPUT_DATE COL VALUE
> ----------- ------- ----------
> 01-SEP-2009 VALUE_2 15
> 02-SEP-2009 VALUE_3 18
> 03-SEP-2009 VALUE_2 12
> 04-SEP-2009 VALUE_1 19
> 05-SEP-2009 VALUE_1 8
> 06-SEP-2009 VALUE_2 12
> 07-SEP-2009 VALUE_2 42
> 08-SEP-2009 VALUE_1 89
> 09-SEP-2009 VALUE_3 22
> 10-SEP-2009 VALUE_3 17
>
> 10 rows selected.
>
> Regards
> Michel- Hide quoted text -
>
> - Show quoted text -
Date: Fri, 18 Sep 2009 11:26:15 -0700 (PDT)
Message-ID: <2ed8ab20-7bd2-4a6f-9c7d-cd168f59af92_at_m20g2000vbp.googlegroups.com>
On Sep 18, 11:19 am, "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
> > > > |
> > > > |
> > > > |
>
> > > > 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 exampleshttp://download.oracle.com/docs/cd/B10501_01/server.920/a96540/functi...
>
> > CASE statement with examplehttp://download.oracle.com/docs/cd/B10501_01/server.920/a96540/expres...
>
> > 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 --- Hide quoted text -
>
> > - Show quoted text -
>
> For your example the code isn't 'pretty':
>
> SQL> create table value_test(
> 2 input_date date primary key,
> 3 value_1 number,
> 4 value_2 number,
> 5 value_3 number
> 6 );
>
> Table created.
>
> SQL>
> SQL> insert all
> 2 into value_test
> 3 values(to_date('01-SEP-09','DD-MON-RR'), 10, 15, 8)
> 4 into value_test
> 5 values(to_date('02-SEP-09','DD-MON-RR'), 12, 5, 18)
> 6 into value_test
> 7 values(to_date('03-SEP-09','DD-MON-RR'), 9, 12, 12)
> 8 into value_test
> 9 values(to_date('04-SEP-09','DD-MON-RR'), 19, 12, 12)
> 10 into value_test
> 11 values(to_date('05-SEP-09','DD-MON-RR'), 8, 1, 2)
> 12 into value_test
> 13 values(to_date('06-SEP-09','DD-MON-RR'), 9, 12, 4)
> 14 into value_test
> 15 values(to_date('07-SEP-09','DD-MON-RR'), 38, 42, 12)
> 16 into value_test
> 17 values(to_date('08-SEP-09','DD-MON-RR'), 89, 89, 89)
> 18 into value_test
> 19 values(to_date('09-SEP-09','DD-MON-RR'), 9, 17, 22)
> 20 into value_test
> 21 values(to_date('10-SEP-09','DD-MON-RR'), 12, 12, 17)
> 22 select * From dual;
>
> 10 rows created.
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> select input_date,
> 2 case when value_1 < value_2 and value_2 < value_3 then
> value_3
> 3 when value_3 < value_2 and value_2 < value_1 then
> value_1
> 4 when value_3 < value_1 and value_1 < value_2 then
> value_2
> 5 when value_3 < value_2 and value_2 = value_1 then
> value_1
> 6 when value_3 = value_2 and value_2 < value_1 then
> value_2
> 7 when value_3 > value_1 and value_1 > value_2 then
> value_3
> 8 when value_3 = value_2 and value_2 > value_1 then
> value_2
> 9 when value_1 > value_2 and value_2 < value_3 then
> value_1
> 10 when value_1 = value_2 and value_2 = value_3 then
> value_1
> 11 when value_1 = value_2 and value_2 < value_3 then
> value_3
> 12 end max_val,
> 13 case when value_1 < value_2 and value_2 < value_3 then
> 'value_3'
> 14 when value_3 < value_2 and value_2 < value_1 then
> 'value_1'
> 15 when value_3 < value_1 and value_1 < value_2 then
> 'value_2'
> 16 when value_3 < value_2 and value_2 = value_1 then
> 'value_1'
> 17 when value_3 = value_2 and value_2 < value_1 then
> 'value_2'
> 18 when value_3 > value_1 and value_1 > value_2 then
> 'value_3'
> 19 when value_3 = value_2 and value_2 > value_1 then
> 'value_2'
> 20 when value_1 > value_2 and value_2 < value_3 then
> 'value_1'
> 21 when value_1 = value_2 and value_2 = value_3 then
> 'value_1'
> 22 when value_1 = value_2 and value_2 < value_3 then
> 'value_3'
> 23 end max_val_col
> 24 from value_test;
>
> INPUT_DATE MAX_VAL MAX_VAL
> -------------------- ---------- -------
> 01-SEP-2009 00:00:00 15 value_2
> 02-SEP-2009 00:00:00 18 value_3
> 03-SEP-2009 00:00:00 12 value_2
> 04-SEP-2009 00:00:00 12 value_2
> 05-SEP-2009 00:00:00 8 value_1
> 06-SEP-2009 00:00:00 12 value_2
> 07-SEP-2009 00:00:00 42 value_2
> 08-SEP-2009 00:00:00 89 value_1
> 09-SEP-2009 00:00:00 22 value_3
> 10-SEP-2009 00:00:00 17 value_3
>
> 10 rows selected.
>
> SQL>
>
> but it does work.
>
> David Fitzjarrell
>
> ---------------------------------------
>
> What I had in mind is the following one:
>
> SQL> select input_date,
> 2 decode(greatest(value_1,value_2,value_3),
> 3 value_1,'VALUE_1',
> 4 value_2,'VALUE_2',
> 5 'VALUE_3') col,
> 6 greatest(value_1,value_2,value_3) value
> 7 from value_test
> 8 order by 1
> 9 /
> INPUT_DATE COL VALUE
> ----------- ------- ----------
> 01-SEP-2009 VALUE_2 15
> 02-SEP-2009 VALUE_3 18
> 03-SEP-2009 VALUE_2 12
> 04-SEP-2009 VALUE_1 19
> 05-SEP-2009 VALUE_1 8
> 06-SEP-2009 VALUE_2 12
> 07-SEP-2009 VALUE_2 42
> 08-SEP-2009 VALUE_1 89
> 09-SEP-2009 VALUE_3 22
> 10-SEP-2009 VALUE_3 17
>
> 10 rows selected.
>
> Regards
> Michel- Hide quoted text -
>
> - Show quoted text -
Many thanks for all your help. It is what I needed. Received on Fri Sep 18 2009 - 13:26:15 CDT