Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id NAA43875 for ; Fri, 26 Nov 1999 13:20:37 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id NAA16211; Fri, 26 Nov 1999 13:19:28 -0500 (EST) To: Stuart Rison cc: Moray McConnachie , pgsql-sql Subject: Re: [SQL] DISTINCT & COUNT In-reply-to: Your message of Fri, 26 Nov 1999 18:00:02 +0000 (GMT) Date: Fri, 26 Nov 1999 13:19:27 -0500 Message-ID: <16209.943640367@sss.pgh.pa.us> From: Tom Lane Stuart Rison writes: > You can however do: > SELECT DISTINCT c.cid > FROM a,b,c > WHERE a.aid=b.aid > AND a.somefield=1 and a.otherfield=2 > AND c.cid=a.cid; > and if your are using a interface (e.g. DBI::DBD::Pg) then you can get > the number of tuples returned... Or do the SELECT into a temp table, followed by fetching count(*) on the temp table. Ugly, but avoids transferring what might be a lot of rows to the frontend. DISTINCT within aggregate functions is on the TODO list, but I don't know when it will happen. regards, tom lane