From: Sjors <jkwast@freeler.nl>
To: pgsql-sql@postgresql.org
Subject: Re: howto/min values
Date: Wed, 19 Mar 2003 14:27:43 +0100
Message-ID: <000801c2ee1b$52cb5460$04a779d9@cc299057a> (raw)
Hi,
My postings seem to double on me. Well thanks to everybody that answered. They 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
1 56 1.23692000
2 96 120.26659366
2 32 0.54865130
etc
Now select for point 1 where id = min(distance). The trick I was suspecting but not been able to grasp is the use of aliases. In Bruce Momjian's "Postgresql: introduction and limitations" was an example like this with structure from the problem here:
select p.points, p.id, p.distance
from table as p
where distance = (select min(distance)
from table as p1
where p.points=p1points)
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
from table as p
where distance = (select min(distance)
from table as p1
where p.points=p1points)
order by points, id
This of course doesn't work, because of the referring to the calculated field 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jkwast@freeler.nl
Subject: Re: howto/min values
In-Reply-To: <000801c2ee1b$52cb5460$04a779d9@cc299057a>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox