Thread
-
Re: Need SQL help, I'm stuck.
wsheldah@lexmark.com — 2001-12-11T14:40:27Z
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 -
Re: Need SQL help, I'm stuck.
Antonio Fiol Bonnín <fiol@w3ping.com> — 2001-12-11T16:00:29Z
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/ >
-
Re: Need SQL help, I'm stuck.
Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2001-12-11T18:55:36Z
On Tue, 11 Dec 2001 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. Distinct on often performs better than subquery options, however it's a PostgreSQL addition, so you need to be careful if you want to run the query on anything else :) > 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, I've generally assumed that subselects in from are effectively a cursor that gets scanned as opposed to running the entire query for each row. I haven't looked to confirm that, but it seems reasonable :)