Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

get the column_name of the maximum value for a row

2 views
Skip to first unread message

Ninja Li

unread,
Sep 17, 2009, 5:45:48 PM9/17/09
to
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

Michel Cadot

unread,
Sep 18, 2009, 12:18:21 AM9/18/09
to

"Ninja Li" <nickl...@gmail.com> a �crit dans le message de news:
328aa22c-967d-4ac1...@33g2000vbe.googlegroups.com...

Use DECODE.
What should be the value if 2 or more columns have the highest value?

Regards
Michel


Ninja Li

unread,
Sep 18, 2009, 7:44:18 AM9/18/09
to
On Sep 18, 12:18 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "Ninja Li" <nickli2...@gmail.com> a écrit dans le message de news:
> 328aa22c-967d-4ac1-b453-8a73b3147...@33g2000vbe.googlegroups.com...

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

Mark D Powell

unread,
Sep 18, 2009, 9:36:50 AM9/18/09
to
> 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

> @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 --

ddf

unread,
Sep 18, 2009, 10:44:16 AM9/18/09
to
> 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
>
>  > @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

Michel Cadot

unread,
Sep 18, 2009, 11:19:09 AM9/18/09
to

"ddf" <ora...@msn.com> a �crit dans le message de news: 9adadbc9-22c9-4138...@h30g2000vbr.googlegroups.com...

On Sep 18, 8:36 am, Mark D Powell <Mark.Pow...@eds.com> wrote:
> On Sep 18, 7:44 am, Ninja Li <nickli2...@gmail.com> wrote:
>
>
>
>
>
> > On Sep 18, 12:18 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
>
> > > "Ninja Li" <nickli2...@gmail.com> a �crit dans le message de news:

Table created.

10 rows created.

SQL>
SQL> commit;

Commit complete.

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


Ninja Li

unread,
Sep 18, 2009, 2:26:15 PM9/18/09
to
On Sep 18, 11:19 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "ddf" <orat...@msn.com> a écrit dans le message de news: 9adadbc9-22c9-4138-90e5-9f3647c28...@h30g2000vbr.googlegroups.com...

> On Sep 18, 8:36 am, Mark D Powell <Mark.Pow...@eds.com> wrote:
>
>
>
>
>
> > On Sep 18, 7:44 am, Ninja Li <nickli2...@gmail.com> wrote:
>
> > > On Sep 18, 12:18 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
>
> > > > "Ninja Li" <nickli2...@gmail.com> a écrit dans le message de news:
> Michel- Hide quoted text -

>
> - Show quoted text -

Many thanks for all your help. It is what I needed.

modu

unread,
Nov 5, 2009, 4:55:59 AM11/5/09
to
On Sep 18, 5:19 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "ddf" <orat...@msn.com> a écrit dans le message de news: 9adadbc9-22c9-4138-90e5-9f3647c28...@h30g2000vbr.googlegroups.com...

> On Sep 18, 8:36 am, Mark D Powell <Mark.Pow...@eds.com> wrote:
>
>
>
>
>
> > On Sep 18, 7:44 am, Ninja Li <nickli2...@gmail.com> wrote:
>
> > > On Sep 18, 12:18 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
>
> > > > "Ninja Li" <nickli2...@gmail.com> a écrit dans le message de news:

> > > > 328aa22c-967d-4ac1-b453-8a73b3147...@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

0 new messages