From jim@luckydigital.com Tue Feb  25 22:30:56 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id D69D0475B33
	for <pgsql-sql@postgresql.org>; Tue, 25 Feb 2003 17:30:56 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 00174-03 for <pgsql-sql@postgresql.org>;
	Tue, 25 Feb 2003 17:30:28 -0500 (EST)
Received: from luckydigital.com (www.luckydigital.com [203.109.157.4])
	by postgresql.org (Postfix) with ESMTP id 1920B475B8C
	for <pgsql-sql@postgresql.org>; Tue, 25 Feb 2003 17:03:23 -0500 (EST)
Received: from jax (203-109-198-1.akl.ihugultra.co.nz [203.109.198.1])
	by luckydigital.com (8.11.6/8.10.0) with SMTP id h1PM3NT30017
	for <pgsql-sql@postgresql.org>; Wed, 26 Feb 2003 11:03:23 +1300
Message-ID: <004a01c2dd19$be6fe400$c900a8c0@jax>
From: "James Cooper" <jim@luckydigital.com>
To: "sql" <pgsql-sql@postgresql.org>
Subject: a change of query
Date: Wed, 26 Feb 2003 11:03:35 +1300
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0047_01C2DD86.B5323D80"
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 amavisd-new
X-Archive-Number: 200302/564
X-Sequence-Number: 11962

This is a multi-part message in MIME format.

------=_NextPart_000_0047_01C2DD86.B5323D80
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

select distinct cp.person_id from cluster_person cp, cluster c where cp.clu=
ster_id =3D c.cluster_id and c.c_id =3D 1

can this query be changed to give just the row count?( of the distinct rows=
 )

yes: i am aware of count() but this wont count the distinct rows - I can ho=
wever achive this with an nested person_id in(select... etc but that slows =
things down big time!!!!

the above quey gives me my resultset asap - is there a way to get just the =
number of rows with the same speed

My thoughts are that I could just make a plpgsql  function with a GET Diagn=
iostics ROW_COUNT -=20

thoughts?



James



------=_NextPart_000_0047_01C2DD86.B5323D80
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1141" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2><FONT color=3D#0000ff size=3D1>
<P>select</FONT><FONT size=3D1> distinct cp.person_id </FONT><FONT color=3D=
#008000=20
size=3D1>from</FONT><FONT size=3D1> cluster_person cp, </FONT><FONT color=
=3D#6d13b7=20
size=3D1>cluster</FONT><FONT size=3D1> c </FONT><FONT color=3D#008000=20
size=3D1>where</FONT><FONT size=3D1> cp.cluster_id =3D c.cluster_id </FONT>=
<FONT=20
color=3D#008000 size=3D1>and</FONT><FONT size=3D1> c.c_id =3D 1</FONT></P>
<P><FONT size=3D1>can this query be changed to give just the row count?( of=
 the=20
distinct rows )</FONT></P>
<P><FONT size=3D1>yes: i am aware of count() but this wont count the distin=
ct rows=20
- I can however achive this with an nested person_id in(select... etc=20
</FONT><FONT size=3D1>but that slows things down big time!!!!</FONT></P>
<P><FONT size=3D1>the above quey gives me my resultset asap - is there a wa=
y to=20
get just the number of rows with the same speed</FONT></P>
<P><FONT size=3D1>My thoughts are that I could just make a plpgsql&nbsp; fu=
nction=20
with a GET Diagniostics ROW_COUNT - </FONT></P>
<P><FONT size=3D1>thoughts?</FONT></P>
<P><FONT size=3D1></FONT>&nbsp;</P>
<P><FONT size=3D1>James</FONT></P>
<P><FONT size=3D1></FONT>&nbsp;</P></FONT></DIV></BODY></HTML>

------=_NextPart_000_0047_01C2DD86.B5323D80--




From bhilton@vpop.net Tue Feb  25 23:04:23 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 57324475D0D
	for <pgsql-sql@postgresql.org>; Tue, 25 Feb 2003 18:04:23 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 11536-04 for <pgsql-sql@postgresql.org>;
	Tue, 25 Feb 2003 18:03:54 -0500 (EST)
Received: from boromir.vpop.net (dns1.vpop.net [207.178.248.2])
	by postgresql.org (Postfix) with ESMTP id 40E9A475C60
	for <pgsql-sql@postgresql.org>; Tue, 25 Feb 2003 17:39:20 -0500 (EST)
Received: from c5cust187.starstream.net (c5cust187.starstream.net
	[206.170.161.187]) by boromir.vpop.net (Postfix) with ESMTP
	id 9D0203A610B; Tue, 25 Feb 2003 14:39:14 -0800 (PST)
Subject: Re: a change of query
From: Brad Hilton <bhilton@vpop.net>
To: James Cooper <jim@luckydigital.com>
Cc: sql <pgsql-sql@postgresql.org>
In-Reply-To: <004a01c2dd19$be6fe400$c900a8c0@jax>
References: <004a01c2dd19$be6fe400$c900a8c0@jax>
Content-Type: text/plain
Organization: 
Message-Id: <1046212757.15608.3.camel@aragorn.vpop.net>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.2.2 
Date: 25 Feb 2003 14:39:17 -0800
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200302/565
X-Sequence-Number: 11963

On Tue, 2003-02-25 at 14:03, James Cooper wrote:
> select distinct cp.person_id from cluster_person cp, cluster c where
> cp.cluster_id = c.cluster_id and c.c_id = 1
> 
> can this query be changed to give just the row count?( of the distinct
> rows )

select count(distinct cp.person_id) from ...

Is that what you were after?

Brad Hilton
VPOP Technologies, Inc.




From chad@weblinkservices.com Tue Feb  25 23:06:01 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 995DF475A37
	for <pgsql-sql@postgresql.org>; Tue, 25 Feb 2003 18:06:01 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 11536-06 for <pgsql-sql@postgresql.org>;
	Tue, 25 Feb 2003 18:05:33 -0500 (EST)
Received: from utahisp.com (utahisp.com [66.239.12.3])
	by postgresql.org (Postfix) with ESMTP id 8B12D475CCE
	for <pgsql-sql@postgresql.org>; Tue, 25 Feb 2003 17:44:02 -0500 (EST)
Received: from chad [63.230.8.76] by utahisp.com
	(SMTPD32-7.14) id A1522B1E0280; Tue, 25 Feb 2003 15:42:26 -0700
Message-ID: <06eb01c2dd1e$ee5488b0$32021aac@chad>
From: "Chad Thompson" <chad@weblinkservices.com>
To: "James Cooper" <jim@luckydigital.com>,
	"sql" <pgsql-sql@postgresql.org>
References: <004a01c2dd19$be6fe400$c900a8c0@jax>
Subject: Re: a change of query
Date: Tue, 25 Feb 2003 15:40:41 -0700
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_06E8_01C2DCE4.40F04EA0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2720.3000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Declude-Sender: chad@weblinkservices.com [63.230.8.76]
X-Note: This E-mail was scanned by Declude JunkMail (www.declude.com) for
 spam.
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200302/567
X-Sequence-Number: 11965

This is a multi-part message in MIME format.

------=_NextPart_000_06E8_01C2DCE4.40F04EA0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

how about=20
select count(distinct cp.person_id) from cluster_person cp, cluster c where=
 cp.cluster_id =3D c.cluster_id and c.c_id =3D 1

Is that as fast?
Chad
  ----- Original Message -----=20
  From: James Cooper=20
  To: sql=20
  Sent: Tuesday, February 25, 2003 3:03 PM
  Subject: [SQL] a change of query


  select distinct cp.person_id from cluster_person cp, cluster c where cp.c=
luster_id =3D c.cluster_id and c.c_id =3D 1

  can this query be changed to give just the row count?( of the distinct ro=
ws )

  yes: i am aware of count() but this wont count the distinct rows - I can =
however achive this with an nested person_id in(select... etc but that slow=
s things down big time!!!!

  the above quey gives me my resultset asap - is there a way to get just th=
e number of rows with the same speed

  My thoughts are that I could just make a plpgsql  function with a GET Dia=
gniostics ROW_COUNT -=20

  thoughts?



  James




------=_NextPart_000_06E8_01C2DCE4.40F04EA0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2722.900" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>how about </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>select count(distinct cp.person_id) <FONT=
=20
color=3D#008000>from</FONT> cluster_person cp, <FONT color=3D#6d13b7>cluste=
r</FONT>=20
c <FONT color=3D#008000>where</FONT> cp.cluster_id =3D c.cluster_id <FONT=
=20
color=3D#008000>and</FONT> c.c_id =3D 1</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Is that as fast?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Chad</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LE=
FT: #000000 2px solid; MARGIN-RIGHT: 0px">
  <DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV>
  <DIV=20
  style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>Fro=
m:</B>=20
  <A title=3Djim@luckydigital.com href=3D"mailto:jim@luckydigital.com">Jame=
s=20
  Cooper</A> </DIV>
  <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A title=3Dpgsql-sql@postgresq=
l.org=20
  href=3D"mailto:pgsql-sql@postgresql.org">sql</A> </DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Tuesday, February 25, 2003 3=
:03=20
  PM</DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Subject:</B> [SQL] a change of query</=
DIV>
  <DIV><BR></DIV>
  <DIV><FONT face=3DArial size=3D2><FONT color=3D#0000ff size=3D1>
  <P>select</FONT><FONT size=3D1> distinct cp.person_id </FONT><FONT color=
=3D#008000=20
  size=3D1>from</FONT><FONT size=3D1> cluster_person cp, </FONT><FONT color=
=3D#6d13b7=20
  size=3D1>cluster</FONT><FONT size=3D1> c </FONT><FONT color=3D#008000=20
  size=3D1>where</FONT><FONT size=3D1> cp.cluster_id =3D c.cluster_id </FON=
T><FONT=20
  color=3D#008000 size=3D1>and</FONT><FONT size=3D1> c.c_id =3D 1</FONT></P>
  <P><FONT size=3D1>can this query be changed to give just the row count?( =
of the=20
  distinct rows )</FONT></P>
  <P><FONT size=3D1>yes: i am aware of count() but this wont count the dist=
inct=20
  rows - I can however achive this with an nested person_id in(select... et=
c=20
  </FONT><FONT size=3D1>but that slows things down big time!!!!</FONT></P>
  <P><FONT size=3D1>the above quey gives me my resultset asap - is there a =
way to=20
  get just the number of rows with the same speed</FONT></P>
  <P><FONT size=3D1>My thoughts are that I could just make a plpgsql&nbsp;=
=20
  function with a GET Diagniostics ROW_COUNT - </FONT></P>
  <P><FONT size=3D1>thoughts?</FONT></P>
  <P><FONT size=3D1></FONT>&nbsp;</P>
  <P><FONT size=3D1>James</FONT></P>
  <P><FONT size=3D1></FONT>&nbsp;</P></FONT></DIV></BLOCKQUOTE></BODY></HTM=
L>

------=_NextPart_000_06E8_01C2DCE4.40F04EA0--




From rbt@rbt.ca Tue Feb  25 23:07:01 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 59639475C26
	for <pgsql-sql@postgresql.org>; Tue, 25 Feb 2003 18:07:01 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 12427-03 for <pgsql-sql@postgresql.org>;
	Tue, 25 Feb 2003 18:06:50 -0500 (EST)
Received: from jester.inquent.com (unknown [216.208.117.7])
	by postgresql.org (Postfix) with ESMTP id 03A574758BD
	for <pgsql-sql@postgresql.org>; Tue, 25 Feb 2003 17:49:32 -0500 (EST)
Received: from [127.0.0.1] (localhost [127.0.0.1])
	by jester.inquent.com (8.12.6/8.12.6) with ESMTP id h1PMo4uD085656;
	Tue, 25 Feb 2003 17:50:05 -0500 (EST) (envelope-from rbt@rbt.ca)
Subject: Re: a change of query
From: Rod Taylor <rbt@rbt.ca>
To: James Cooper <jim@luckydigital.com>
Cc: sql <pgsql-sql@postgresql.org>
In-Reply-To: <004a01c2dd19$be6fe400$c900a8c0@jax>
References: <004a01c2dd19$be6fe400$c900a8c0@jax>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-uU2nVm3NjEPY7bW7KSU3"
Organization: 
Message-Id: <1046213403.32353.50.camel@jester>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.2.2 
Date: 25 Feb 2003 17:50:04 -0500
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200302/566
X-Sequence-Number: 11964

--=-uU2nVm3NjEPY7bW7KSU3
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Tue, 2003-02-25 at 17:03, James Cooper wrote:
> select distinct cp.person_id from cluster_person cp, cluster c where
> cp.cluster_id =3D c.cluster_id and c.c_id =3D 1
>=20
> can this query be changed to give just the row count?( of the distinct
> rows )

:) So close

select count(distinct cp.person_id) from...

--=20
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

--=-uU2nVm3NjEPY7bW7KSU3
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (FreeBSD)

iD8DBQA+W/Mb6DETLow6vwwRAnaKAJ9r2/F3dY+PT7k4l0TFPAprAgwmewCcDF8P
bpArkOa5UNzsUg7YM5ePulk=
=GTdM
-----END PGP SIGNATURE-----

--=-uU2nVm3NjEPY7bW7KSU3--