X-Original-To: pgsql-sql@postgresql.org Received: from mail6-sh.home.nl (mail6.home.nl [213.51.128.22]) by postgresql.org (Postfix) with ESMTP id A205D474E4F for ; Wed, 19 Mar 2003 08:27:31 -0500 (EST) Received: from cc299057a ([217.121.167.4]) by mail6-sh.home.nl (InterMail vM.5.01.05.17 201-253-122-126-117-20021021) with SMTP id <20030319132734.LRFN21331.mail6-sh.home.nl@cc299057a> for ; Wed, 19 Mar 2003 14:27:34 +0100 Message-ID: <000801c2ee1b$52cb5460$04a779d9@cc299057a> From: "Sjors" To: Subject: Re: howto/min values Date: Wed, 19 Mar 2003 14:27:43 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0005_01C2EE23.B4434A10" 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-Archive-Number: 200303/454 X-Sequence-Number: 12493 This is a multi-part message in MIME format. ------=_NextPart_000_0005_01C2EE23.B4434A10 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, My postings seem to double on me. Well thanks to everybody that answered. T= hey were not the answers I was hoping for but, I found one in the docs. Let= me explain again what I'm doing with a example: table: points id distance 1 25 0.26598333 1 32 0.65983600=20=20=20=20 1 56 1.23692000 2 96 120.26659366 2 32 0.54865130 etc Now select for point 1 where id =3D min(distance). The trick I was suspecti= ng but not been able to grasp is the use of aliases. In Bruce Momjian's "Po= stgresql: introduction and limitations" was an example like this with struc= ture from the problem here: select p.points, p.id, p.distance=20 from table as p where distance =3D (select min(distance)=20 from table as p1 where p.points=3Dp1points) order by points, id Now this works. So going to the next question, if distance is a calculated = field how can I use it in the subquery. How do you add another alias in: select p.points, p.id, (x-x) as distance=20 from table as p where distance =3D (select min(distance)=20 from table as p1 where p.points=3Dp1points) order by points, id =20 This of course doesn't work, because of the referring to the calculated fie= ld wich is not actually a part of the table. Can you do this somehow or is = it impossible? Love to hear from you guys, greetz Sjors ------=_NextPart_000_0005_01C2EE23.B4434A10 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi,

My postings seem to double on me. Well tha= nks=20 to everybody that answered. They were not the answers I was hopin= g for=20 but, I found one in the docs. Let me explain again what I'm doing with a=20 example:

table:

points id &nb= sp;=20 distance

1 =20 25 0.26598333

1 =20 32 0.65983600 =20

1 =20 56 1.23692000

2 =20 96 120.26659366

2 =20 32 0.54865130

etc

Now select for point 1 where id =3D min(di= stance).=20 The trick I was suspecting but not been able to grasp is the use of aliases= . In=20 Bruce Momjian's "Postgresql: introduction and limitations" was an example l= ike=20 this with structure from the problem here:

select p.points, p.id, p.distance <= /DIV>

from table as p

where distance =3D (select min(distance)= =20

=20 = =20 from table as p1

=20 = =20 where p.points=3Dp1points)

order by points, id

Now this works. So going to the next quest= ion, if=20 distance is a calculated field how can I use it in the subquery.=20 How do you add another alias in:

select p.points, p.id, (x-x) as distance= =20

from table as p

where distance =3D (select min(distance)= =20

=20 = =20 from table as p1

=20 = =20 where p.points=3Dp1points)

order by points, id

This of course doesn't work, because of th= e=20 referring to the calculated field wich is not actually a part of the=20 table. Can you do this somehow or is it impossible?

Love to hear from you guys,

greetz Sjors

------=_NextPart_000_0005_01C2EE23.B4434A10--