"ddf" <orat... at (no spam) msn.com> a écrit dans le message de news: 9adadbc9-22c9-4138-90e5-9f3647c28... at (no spam) h30g2000vbr.googlegroups.com...
On Sep 18, 8:36 am, Mark D Powell <Mark.Pow... at (no spam) eds.com> wrote:
On Sep 18, 7:44 am, Ninja Li <nickli2... at (no spam) gmail.com> wrote:
On Sep 18, 12:18 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
"Ninja Li" <nickli2... at (no spam) gmail.com> a écrit dans le message de news:
328aa22c-967d-4ac1-b453-8a73b3147... at (no spam) 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 (no spam) 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,
4 into value_test
5 values(to_date('02-SEP-09','DD-MON-RR'), 12, 5, 1
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 -
