From jkwast@freeler.nl Wed Mar 19 13:27:31 2003 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-- From ch@rodos.fzk.de Wed Mar 19 13:54:02 2003 X-Original-To: pgsql-sql@postgresql.org Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52]) by postgresql.org (Postfix) with SMTP id AAA30474E4F for ; Wed, 19 Mar 2003 08:54:02 -0500 (EST) Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ; Wed Mar 19 14:54:04 2003 +0100 Received: œby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id OAA09691; Wed, 19 Mar 2003 14:54:04 +0100 (MET) Message-ID: <3E78767B.47CA0F73@rodos.fzk.de> Date: Wed, 19 Mar 2003 14:54:03 +0100 From: Christoph Haller X-Mailer: Mozilla 4.77 [en] (X11; U; HP-UX B.10.20 9000/780) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Cc: jkwast@freeler.nl Subject: Re: howto/min values Content-Type: text/plain; charset=iso-8859-2 Content-Transfer-Encoding: 7bit X-Archive-Number: 200303/456 X-Sequence-Number: 12495 > My postings seem to double on me. It's standard procedure to reply twice, one to the list and one to the sender, because you don't need to subscribe to the list to get the answers. But if you are subscribed and ask a question you usually get two. > > 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? > No, not at all. Something like select p.points, p.id, p.distance from (select points, id, (x-x) as distance from table) as p where p.distance = (select min(p1.distance) > from table as p1 > where p.points=p1.points) > order by p.points, p.id ; should do the trick. The FROM clause allows any kind of subqueries within parentheses. Regards, Christoph From jkwast@freeler.nl Wed Mar 19 15:07:33 2003 X-Original-To: pgsql-sql@postgresql.org Received: from mail5-sh.home.nl (mail5.home.nl [213.51.128.16]) by postgresql.org (Postfix) with ESMTP id 4D285474E4F for ; Wed, 19 Mar 2003 10:07:33 -0500 (EST) Received: from cc299057a ([217.121.167.4]) by mail5-sh.home.nl (InterMail vM.5.01.05.17 201-253-122-126-117-20021021) with SMTP id <20030319150736.KOP17325.mail5-sh.home.nl@cc299057a> for ; Wed, 19 Mar 2003 16:07:36 +0100 Message-ID: <002b01c2ee29$4c57d690$04a779d9@cc299057a> From: "Sjors" To: References: <3E78767B.47CA0F73@rodos.fzk.de> Subject: Re: howto/min values Date: Wed, 19 Mar 2003 16:07:45 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: 7bit 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/463 X-Sequence-Number: 12502 > > > No, not at all. Something like > > select p.points, p.id, p.distance from > (select points, id, (x-x) as distance from table) as p > where p.distance = (select min(p1.distance) > > from table as p1 > > where p.points=p1.points) > > order by p.points, p.id ; > > should do the trick. > The FROM clause allows any kind of subqueries within parentheses. > > Regards, Christoph > Thanks, this is the solution. greetz Sjors