Thread: Re: howto/min values

Re: howto/min values

From
Christoph Haller
Date:
> 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





Re: howto/min values

From
"Sjors"
Date:
> >
> 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