What about this one, which also happens to give the right result?
select B.* from T1 A RIGHT JOIN T1 B on (A.C1=B.C1 AND A.C2>B.C2) where
A.c1 is null;
It is really amazing how many different ways there are to express the
same wishes in SQL...
Compared to the following ones, it is efficient:
SELECT A.* FROM T1 A WHERE NOT EXISTS (select * from T1 B where B.C2 >
A.C2 AND B.C1=A.C1);
SELECT * FROM T1 EXCEPT SELECT A.* FROM T1 A, T1 B where A.C1=B.C1 AND
A.C2<B.C2;
Though, the following is AMAZINGLY efficient. Only a seq scan, plus some
post processing.
select distinct on (C1) C1, C2, C3, C4 from T1 order by C1, C2 desc;
I think that distinct/order by combination is best suited for your needs.
Does anyone know of a "master source of knowledge" where one could learn
to choose an appropriate formulation for a SQL query without trying all
of the imaginable possibilities with EXPLAIN?
Thank you all!
Antonio
wsheldah@lexmark.com wrote:
>
>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/
>