Re: Need SQL help, I'm stuck.

Try using a subquery to identify the max(C2) value,
then join T1 to the result of the subselect by C1 and
limit the results where C2 equals max(C2).

I think the following should work; but I've never done
a subquery, so you may have to tweak the syntax:

select C1, C2, C3, C4 from T1,(select C1 as M1,
max(C2) as M2 from T1 group by M1) as T2
where T1.C1=T2.M1 and T1.C2=T2.M2;

Best of luck,

Andrew Gould

--- Chris Albertson <chrisalbertson90278@yahoo.com>
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.
>
>
> 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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org


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