Re: max values

Hi,
What is the datatype of your code column? If it is varchar2 instead of number, this is an ascii based group by and correct. To resolve it you will need either to change the datatype or
select to_number(code)
from temp
group by to_number(code)
The other question: this is a limitation of many sql implementations. The sql in access has the functions last and first to resolve this. You will need to write this as
select name, code
from temp
where name =
(select max(name) from temp)

This should work.

Hth,

Sybrand Bakker, Oracle DBA

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

Received on Sat Jan 09 1999 - 00:56:36 CST