another tricky SQL - get maximum value from ONE record?

[KittyCatAngel]

Hello!

I have a table that looks like this
name char
answer1 int
answer2 int
answer3 int
answer4 int
answer5 int

How do I get the maximum answer from one record?

So, I have records like these:
'Record1', 50, 10, 80, 9, 49
'Record2', 10, 79, 28, 3, 306

How do I get the max answer for record 'Record1' -which is 80? Is there a way?

I need a max comand like this:
Select max(answer1, answer2, answer3, answer4, answer5) where name = 'Record1'
that will return 80 as the result.

This has to be in one statement (can't have another local variable) or a view. Also, this design cannot be changed.

Any help is appreciated. Thanks!!!

[stecal]

Sounds more like a homework problem than a real life one.

[KittyCatAngel]

Huh?

This is not a HW problem. I'm a developer (consultant) on a project. We are working on a pretty poorly-designed db schema created from a 3rd party vendor. Hence, no control over design (I'm sure most of the consultants out there understand these frustrations)

[mike9]

Have a look at the SQL function GREATEST and combine it with MAX.

This should solve your problem.

Mike

[stecal]

SQL> insert into kitty values ('CAT', 12, 34, 11, 56, 80);

1 row created.

SQL> insert into kitty values ('ANGEL', 45, 54, 34, 23, 11);

1 row created.

SQL> select * from kitty;

NAME             ANS1       ANS2       ANS3       ANS4       ANS5
---------- ---------- ---------- ---------- ---------- ----------
CAT                12         34         11         56         80
ANGEL              45         54         34         23         11

SQL> select name, greatest(ans1, ans2, ans3, ans4, ans5) from kitty;

NAME       GREATEST(ANS1,ANS2,ANS3,ANS4,ANS5)
---------- ----------------------------------
CAT                                        80
ANGEL                                      54

[stecal]

SQL> select greatest(ans1, ans2, ans3, ans4, ans5) "GREATEST"
  2  from kitty
  3  where name = 'CAT';

  GREATEST
----------
        80