Re: max values

hpcheong_at_ncs.com.sg wrote:
>
> hi all,
>
> i like to ask about the following.
>
> SQL> select * from temp;
>
> CODE NAME ADDRESS
> ---------- ---------- ---------
> 12 jane abc
> 15 peter qwe
> 10 pat poi
> 9 leo lkj
> 8 eve dfg
>
> when i do a <select max(code) from temp;>, the result is
>
> MAX(CODE)
> ----------
> 9
>
> instead of 15. what is the correct syntax in order to have 15 to be the
> expected result.
>
> secondly, with the max values got from code, i would want to have the name's
> result as well which should be peter. what is the syntax? when i do this
> <select name, code from temp where name = max(name);>, error is ORA-00934:
> group function is not allowed here. any idea?
>
> could it because oracle 7.2 do not allow such function?
>
> pls help..
> thanks in advance!
> evelyn.
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Evelyn,

Try #1:

  select
    max(to_number(code))
  from
    temp;

And #2:

  select
    name
  from
    temp
  where
    to_number(code)=(select max(to_number(code)) from temp);

The second query could result in multiple rows if there are several 'code' values that are maximums.

Yours,
Geoff Houck
systems hk
hksys_at_teleport.com
http://www.teleport.com/~hksys Received on Sat Jan 09 1999 - 01:21:50 CST