pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: howto/min values
3+ messages / 2 participants
[nested] [flat]
* Re: howto/min values
@ 2003-03-19 13:27 Sjors <jkwast@freeler.nl>
0 siblings, 0 replies; 3+ messages in thread
From: Sjors @ 2003-03-19 13:27 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: howto/min values
@ 2003-03-19 13:54 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 3+ messages in thread
From: Christoph Haller @ 2003-03-19 13:54 UTC (permalink / raw)
To: pgsql-sql; +Cc: jkwast@freeler.nl
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: howto/min values
@ 2003-03-19 15:07 Sjors <jkwast@freeler.nl>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 3+ messages in thread
From: Sjors @ 2003-03-19 15:07 UTC (permalink / raw)
To: pgsql-sql
> >
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-03-19 15:07 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-19 13:27 Re: howto/min values Sjors <jkwast@freeler.nl>
2003-03-19 13:54 Re: howto/min values Christoph Haller <ch@rodos.fzk.de>
2003-03-19 15:07 ` Sjors <jkwast@freeler.nl>
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