Re: Very simple SQL question ... or maybe not

On Wed, 04 Apr 2001 01:08:31 GMT, "Mike Moore" <hicamel_at_mail.home.com> wrote

        in <juuy6.91$4L4.60787_at_news1.frmt1.sfba.home.com>:
> Given my_table and data:
>
> col_a col_b col_c
> A 7 cat
> A 2 dog
> B 4 zebra
> B 8 buraq
>
> the output should be
>
> A 7 cat
> B 8 buraq
>
> in other words... For each unique col_a, select the largest col_b and it's
> related col_c.
>
> I can do it this like
>
> select col_a, max(col_b||' '||col_c) from my_table group by col_a;
>
> but I suspect there is a more elegant solution.

Try this:

select col_a, col_b, col_c
from my_table a
where a.col_b = (select max(b.col_b)

                 from my_table b
                 where b.col_a = a.col_a
                );

Remco

-- 
qn-195-66-31-144:  01:35:02 up 19 days,  6:27, 11 users,  load average: 3.49, 3.18, 2.73