From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Chris Albertson <chrisalbertson90278(at)yahoo(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Need SQL help, I'm stuck. |
Date: | 2001-12-10 22:27:29 |
Message-ID: | 20011210142346.M70079-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | bpalmer | 2001-12-10 22:34:00 | Re: problems doing sub-selects on PostgreSQL 7.1.3 and |
Previous Message | wsheldah | 2001-12-10 22:26:49 | Re: Need SQL help, I'm stuck. |