Re: Need SQL help, I'm stuck.

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/
>