Re: Max Function Question

Using a pair comparison my help.

SQL> select * from t;

        A B C
--------- --------- ---------

        1         1         1
        1         2         3
        2         2         2
        2         3         5

SQL> select * from t
  2 where (a,b) in (select a,max(b) from t group by a);

        A B C
--------- --------- ---------

        1         2         3
        2         3         5

SQL> In article <7ke06m$nft$1_at_nnrp1.deja.com>,   tabates_at_my-deja.com 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
>
> 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.
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Jun 18 1999 - 16:16:19 CDT