Re: Need SQL help, I'm stuck.

On Mon, 10 Dec 2001, Chris Albertson wrote:

> Help. I seem to have a case of "brain lock" and can't figure out
> something that I should know is simple.
>
> Here is what I am trying to do.  Let's say I have a table called
> T1 with columns C1, C2, C3, C4.  It contains data as follows
>
>   a  1  abcd  dfg
>   a  2  cvfr  erg
>   a  3  derg  hbg
>   b  1  cccc  rth
>   c  1  rdvg  egt
>   c  2  derf  ett
>
> I want a SQL query that returns these rows
>
>   a  3  derg  hbg
>   b  1  cccc  rth
>   c  2  derf  ett
>
> All I can think of is
>
>    SELECT C1, max(C2), C3, C4 FROM T1 GROUP BY C1;
>
> That does not work.  What I really want is the values for C1, C3
> and C4 that are associated with the row containing the maximum
> value of C2 for each group of like C1 values.  I don't even need
> to know what is max(C2).
>
> Can I join the table with itself somehow?  See: "brain lock".
> This should not be hard.

Maybe something like?
select t1.c1, c3, c4 from t1, (select c1, max(c2) as c2 from t1 group by
c1) foo where t1.c1=foo.c1 and t1.c2=foo.c2;