Re: Max Function Question

A copy of this was sent to tabates_at_my-deja.com (if that email address didn't require changing) On Fri, 18 Jun 1999 17:38:32 GMT, you wrote:

>For purposes of evaluation let us assume the following:
>There is one table called Table1 with three columns a, b, c
>populated with the following data
>
>a b c
>- - -
>1 1 1
>1 2 3
>2 2 2
>2 3 5
>
>My desired resultant record set would be
>a b c
>- - -
>1 2 3
>2 3 5
>

that looks alot like:

select a, max(b), max(c) from T group by a;

>The following function is close to what I need but not quite it.
> Select a, mac(b) from Table1 group by a;
>
>There is a third column c whose value I wish to include.
> Select a, c, max(b) from Table1 group by a;
>Will not work since there is not aggregate function for column c.
>
>Also the following is close but still lacking
> Select a, b, c from Table1 where
> a in (select a from Table1 group by a) and
> b in (select max(b) from Table1 group by a);
>Returns:
>a b c
>- - -
>1 2 3
>2 2 2 <--Not desired
>2 3 5
>
>Hope I have made my needs clear and am thankful for all help
>
>TABates
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Fine Grained Access Control", added June 8'th  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jun 18 1999 - 14:41:50 CDT