Need SQL help, I'm stuck.

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.


Thanks,


=====
Chris Albertson
  Home:   310-376-1029  chrisalbertson90278@yahoo.com
  Cell:   310-990-7550
  Office: 310-336-5189  Christopher.J.Albertson@aero.org

__________________________________________________
Do You Yahoo!?
Send your FREE holiday greetings online!
http://greetings.yahoo.com