Re: howto/min values - Mailing list pgsql-sql

From Sjors
Subject Re: howto/min values
Date
Msg-id 000801c2ee1b$52cb5460$04a779d9@cc299057a
Whole thread Raw
List pgsql-sql
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

pgsql-sql by date:

Previous
From: Achilleus Mantzios
Date:
Subject: Re: Listing Users
Next
From: "A.Bhuvaneswaran"
Date:
Subject: Re: vacuum all but system tables