 | |
get the column_name of the maximum value for a row
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
get the column_name of the maximum value for a row -
09-17-2009
, 04:45 PM
#2
| |||
| |||
|
|
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 |
What should be the value if 2 or more columns have the highest value?
Regards
Michel
#3
| |||
| |||
|
|
"Ninja Li" <nickli2... (AT) gmail (DOT) com> a écrit dans le message de news: 328aa22c-967d-4ac1-b453-8a73b3147... (AT) 33g2000vbe (DOT) 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 |
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
#4
| ||||
| ||||
|
|
On Sep 18, 12:18*am, "Michel Cadot" <micadot{at}altern{dot}org> wrote: "Ninja Li" <nickli2... (AT) gmail (DOT) com> a écrit dans le message de news: 328aa22c-967d-4ac1-b453-8a73b3147... (AT) 33g2000vbe (DOT) 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 - |
decode is basically the same functionality. when colA > colB and colA
Quote:
|
colC then 'colA '||value_a |
Decode with examples
http://download.oracle.com/docs/cd/B...htm#SQLRF00631
CASE statement with example
http://download.oracle.com/docs/cd/B...5a.htm#1033394
If you did not need to know the column you could use the GREATEST
function.
Quote:
|
set echo on select * from marktest4; |
---------- ---------- ----------
7 8 9
9 8 7
Quote:
|
@t19 select case when fld1 > fld2 and fld1 > fld3 then 'FLD1 '||fld1 2 when fld2 > fld1 and fld2 > fld3 then 'FLD2 '||fld2 |
4 else 'Error' end "VALUE"
5 from marktest4
6 /
VALUE
---------------------------------------------
FLD3 9
FLD1 9
HTH -- Mark D Powell --
#5
| |||
| |||
|
|
On Sep 18, 7:44*am, Ninja Li <nickli2... (AT) gmail (DOT) com> wrote: On Sep 18, 12:18*am, "Michel Cadot" <micadot{at}altern{dot}org> wrote: "Ninja Li" <nickli2... (AT) gmail (DOT) com> a écrit dans le message de news: 328aa22c-967d-4ac1-b453-8a73b3147... (AT) 33g2000vbe (DOT) 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 usePL/ | 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 *> @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 - |
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
#6
| |||
| |||
|
|
On Sep 18, 7:44 am, Ninja Li <nickli2... (AT) gmail (DOT) com> wrote: On Sep 18, 12:18 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote: "Ninja Li" <nickli2... (AT) gmail (DOT) com> a écrit dans le message de news: 328aa22c-967d-4ac1-b453-8a73b3147... (AT) 33g2000vbe (DOT) 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 @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 - |
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
#7
| |||
| |||
|
|
"ddf" <orat... (AT) msn (DOT) com> a écrit dans le message de news: 9adadbc9-22c9-4138-90e5-9f3647c28... (AT) h30g2000vbr (DOT) googlegroups.com... On Sep 18, 8:36 am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote: On Sep 18, 7:44 am, Ninja Li <nickli2... (AT) gmail (DOT) com> wrote: On Sep 18, 12:18 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote: "Ninja Li" <nickli2... (AT) gmail (DOT) com> a écrit dans le message de news: 328aa22c-967d-4ac1-b453-8a73b3147... (AT) 33g2000vbe (DOT) 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 @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 - |
#8
| |||
| |||
|
|
"ddf" <orat... (AT) msn (DOT) com> a écrit dans le message de news: 9adadbc9-22c9-4138-90e5-9f3647c28... (AT) h30g2000vbr (DOT) googlegroups.com... On Sep 18, 8:36 am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote: On Sep 18, 7:44 am, Ninja Li <nickli2... (AT) gmail (DOT) com> wrote: On Sep 18, 12:18 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote: "Ninja Li" <nickli2... (AT) gmail (DOT) com> a écrit dans le message de news: 328aa22c-967d-4ac1-b453-8a73b3147... (AT) 33g2000vbe (DOT) 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
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.