Received: from hamlet.cnmnetwork.com (hamlet.cnmnetwork.com [209.79.28.33]) by hub.org (8.9.3/8.9.3) with SMTP id QAA05181 for ; Wed, 16 Jun 1999 16:07:26 -0400 (EDT) (envelope-from rgireyev@cnmnetwork.com) Message-Id: <199906162007.QAA05181@hub.org> Received: (qmail 3944 invoked from network); 16 Jun 1999 13:07:18 -0700 Received: from 209-79-252-225.max-tnt-04.wla.ca.us.cnmnetwork.com (HELO via) (209.79.252.225) by hamlet.cnmnetwork.com with SMTP; 16 Jun 1999 13:07:18 -0700 From: "Rudy Gireyev" To: pgsql-sql@postgreSQL.org Date: Wed, 16 Jun 1999 13:08:15 -0700 MIME-Version: 1.0 Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Subject: Re: [SQL] DISTINCT count(*), possible? Reply-to: rgireyev@cnmnetwork.com Priority: normal References: Your message of Wed, 16 Jun 1999 09:55:37 +0100 In-reply-to: <24839.929543079@sss.pgh.pa.us> X-mailer: Pegasus Mail for Win32 (v3.01d) Out of curiosity, more than anything else. Would the group by solve this problem? Rudy On 16 Jun 99, at 10:24, Tom Lane wrote: > Another way is > > SELECT DISTINCT field INTO temp_table FROM mytable; > SELECT count(*) FROM temp_table; > DROP TABLE temp_table; > > which is arguably more efficient than the previous solution > for large tables --- it should involve O(n log n) work rather > than O(n^2). For a small table, the overhead of creating and > dropping a table might overshadow the actual work, though. > > In 6.5 you can use "INTO TEMP temp_table" and avoid worrying > about having to invent distinct temp table names for concurrent > users of the database. > > The SQL-standard "SELECT count(DISTINCT field)" would be even nicer, > of course, but I dunno when someone will get around to it... > > regards, tom lane > >