In just eyeballing the various responses, it looks like the one using DISTINCT
ON manages to avoid using a subquery at all. Would this give it the edge in
performance? I had somehow never noticed the DISTINCT ON syntax before, this
looks very handy.
Also, my first attempt was to put the subquery in the WHERE clause, but I
noticed that several put the subquery in the FROM clause. Does putting it in the
FROM clause just run it once, with the results of the run joined to the outer
tables? It certainly seemed like putting the query in the WHERE clause was
running it for every row. Thanks,
Wes Sheldahl
Martijn van Oosterhout <kleptog%svana.org@interlock.lexmark.com> on 12/10/2001
06:33:59 PM
Please respond to Martijn van Oosterhout
<kleptog%svana.org@interlock.lexmark.com>
To: Chris Albertson <chrisalbertson90278%yahoo.com@interlock.lexmark.com>
cc: pgsql-general%postgresql.org@interlock.lexmark.com (bcc: Wesley
Sheldahl/Lex/Lexmark)
Subject: Re: [GENERAL] Need SQL help, I'm stuck.
On Mon, Dec 10, 2001 at 01:42:54PM -0800, 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
>
How about:
select distinct on (C1) C1, C2, C3, C4 from T1 order by C1, C2 desc;
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> Terrorists can only take my life. Only my government can take my freedom.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org