So everything is ordered like statement 1 = but=20 includes the count(uid) from the points DB like statement 2 returns<= /DIV>

Any ideas on an effecient way of doing=20 this?

------=_NextPart_000_0099_01C2751F.F989F880-- From olly@lfix.co.uk Wed Oct 16 20:39:02 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id CF5D3476831 for ; Wed, 16 Oct 2002 16:39:02 -0400 (EDT) Received: from anchor-post-39.mail.demon.net (anchor-post-39.mail.demon.net [194.217.242.80]) by postgresql.org (Postfix) with ESMTP id 1464747674F for ; Wed, 16 Oct 2002 16:38:59 -0400 (EDT) Received: from lfix.demon.co.uk ([158.152.59.127] helo=linda.lfix.co.uk) by anchor-post-39.mail.demon.net with esmtp (Exim 3.36 #2) id 181uwV-00050O-0U; Wed, 16 Oct 2002 21:38:59 +0100 Received: from localhost ([127.0.0.1] helo=localhost.localdomain ident=olly) by linda.lfix.co.uk with esmtp (Exim 3.36 #1 (Debian)) id 181uwV-0006vd-00; Wed, 16 Oct 2002 21:38:59 +0100 Subject: Re: Help with SQL From: Oliver Elphick To: "Eric L. Blevins" Cc: pgsql-sql@postgresql.org In-Reply-To: <009c01c27541$83bf46b0$0201a8c0@Gateway> References: <009c01c27541$83bf46b0$0201a8c0@Gateway> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 Date: 16 Oct 2002 21:38:59 +0100 Message-Id: <1034800739.22821.105.camel@linda> Mime-Version: 1.0 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/183 X-Sequence-Number: 9900 On Wed, 2002-10-16 at 19:26, Eric L. Blevins wrote: > I've got 2 SQL statements I would like to combine into one. ... > statement 1: SELECT uid, count(uid) FROM triangulated WHERE uid != 'anonymus' AND uid > != 'anonymous' AND uid != '' GROUP BY uid ORDER BY count DESC LIMIT 10; ... > statement 2: SELECT uid, count(uid) FROM points WHERE uid != 'anonymus' AND uid != > 'anonymous' AND uid != '' GROUP BY uid ORDER BY count DESC LIMIT 10; ... > what I want to do is have one statement that returns something like this: > uid | count1 | count2 > eblevins 1179 23595 > DaClyde 398 11031 > Drew 30 104 > zombiechick 3 159 > > So everything is ordered like statement 1 but includes the count(uid) from the points DB like statement 2 returns SELECT * FROM (SELECT uid, count(uid) AS count1 FROM triangulated WHERE uid != 'anonymus' AND uid != 'anonymous' AND uid != '' GROUP BY uid) AS c1 LEFT JOIN (SELECT uid, count(uid) AS count2 FROM points WHERE uid != 'anonymus' AND uid != 'anonymous' AND uid != '' GROUP BY uid) AS c2 ORDER BY count1 DESC LIMIT 10; (Apologies for syntax errors, if any - I haven't tried it out,) I used LEFT JOIN because you are ordering by count1, so you probably won't want any rows where count1 is null. If the total of rows from subselect c1 was likely to be less than 10, you might want to do a FULL JOIN and order by count1, count2. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "But be ye doers of the word, and not hearers only, deceiving your own selves." James 1:22 From eblevins@insight.rr.com Wed Oct 16 21:01:22 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 03E8C476795 for ; Wed, 16 Oct 2002 17:01:22 -0400 (EDT) Received: from clmboh1-smtp5.columbus.rr.com (clmboh1-smtp5.columbus.rr.com [65.24.0.116]) by postgresql.org (Postfix) with ESMTP id A1C07476780 for ; Wed, 16 Oct 2002 17:01:20 -0400 (EDT) Received: from Gateway (dhcp065-031-003-115.insight.rr.com [65.31.3.115]) by clmboh1-smtp5.columbus.rr.com (8.11.2/8.11.2) with SMTP id g9GL1K016096; Wed, 16 Oct 2002 17:01:20 -0400 (EDT) Message-ID: <00c301c27557$2c2cc010$0201a8c0@Gateway> From: "Eric L. Blevins" To: "Oliver Elphick" Cc: References: <009c01c27541$83bf46b0$0201a8c0@Gateway> <1034800739.22821.105.camel@linda> Subject: Re: Help with SQL Date: Wed, 16 Oct 2002 17:01:12 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/184 X-Sequence-Number: 9901 This is what I ended up with: SELECT c1.uid, count1, count2 FROM (SELECT uid, count(uid) AS count1 FROM triangulated WHERE uid != 'anonymus' AND uid != 'anonymous' AND uid != '' GROUP BY uid) AS c1 LEFT JOIN (SELECT uid,count(uid) AS count2 FROM points WHERE uid != 'anonymus' AND uid != 'anonymous' AND uid != '' GROUP BY uid) as c2 on (c1.uid = c2.uid) ORDER BY count1 DESC LIMIT 10; I got the results I wanted! uid | count1 | count2 -------------+--------+-------- eblevins | 1179 | 23595 DaClyde | 398 | 11031 Drew | 30 | 104 zombiechick | 3 | 159 (4 rows) Thanks for your help! Eric L. Blevins ----- Original Message ----- From: "Oliver Elphick" To: "Eric L. Blevins" Cc: Sent: Wednesday, October 16, 2002 4:38 PM Subject: Re: [SQL] Help with SQL > On Wed, 2002-10-16 at 19:26, Eric L. Blevins wrote: > > I've got 2 SQL statements I would like to combine into one. > ... > > statement 1: SELECT uid, count(uid) FROM triangulated WHERE uid != 'anonymus' AND uid > > != 'anonymous' AND uid != '' GROUP BY uid ORDER BY count DESC LIMIT 10; > ... > > statement 2: SELECT uid, count(uid) FROM points WHERE uid != 'anonymus' AND uid != > > 'anonymous' AND uid != '' GROUP BY uid ORDER BY count DESC LIMIT 10; > ... > > what I want to do is have one statement that returns something like this: > > uid | count1 | count2 > > eblevins 1179 23595 > > DaClyde 398 11031 > > Drew 30 104 > > zombiechick 3 159 > > > > So everything is ordered like statement 1 but includes the count(uid) from the points DB like statement 2 returns > > SELECT * FROM > (SELECT uid, count(uid) AS count1 > FROM triangulated > WHERE uid != 'anonymus' AND > uid != 'anonymous' AND > uid != '' > GROUP BY uid) AS c1 > LEFT JOIN > (SELECT uid, count(uid) AS count2 > FROM points > WHERE uid != 'anonymus' AND > uid != 'anonymous' AND > uid != '' > GROUP BY uid) AS c2 > ORDER BY count1 DESC > LIMIT 10; > > (Apologies for syntax errors, if any - I haven't tried it out,) > > I used LEFT JOIN because you are ordering by count1, so you probably > won't want any rows where count1 is null. If the total of rows from > subselect c1 was likely to be less than 10, you might want to do a FULL > JOIN and order by count1, count2. > > -- > Oliver Elphick Oliver.Elphick@lfix.co.uk > Isle of Wight, UK > http://www.lfix.co.uk/oliver > GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C > ======================================== > "But be ye doers of the word, and not hearers only, > deceiving your own selves." James 1:22 > >