Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [206.210.65.6]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA17684 for ; Wed, 16 Jun 1999 10:25:32 -0400 (EDT) (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 KAA24841; Wed, 16 Jun 1999 10:24:39 -0400 (EDT) To: Stuart Rison cc: pgsql-sql@postgreSQL.org, matthew@venux.net Subject: Re: [SQL] DISTINCT count(*), possible? In-reply-to: Your message of Wed, 16 Jun 1999 09:55:37 +0100 Date: Wed, 16 Jun 1999 10:24:39 -0400 Message-ID: <24839.929543079@sss.pgh.pa.us> From: Tom Lane 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