Thread: howto?

howto?

From
"Sjors"
Date:
Hi,
 
Being not able to grasp in full sql, I've decided to call on the list. I've got a table with points, place_id and distance between points and place_id. For each point there can be more place_id's but I want to selesct the one with the minimum distance. Now I can group by the points and select the min(distance) but I can't do this if place_id is involved. I used a statment like this:
 
Select points, place_id, distance from table
group by points, place_id, distance
having count(*)>1 and distance=(select min(distance) from table
 
This will give me 1 value being the minimum of all distances.
If only done on basis of points and distance I do get the minimum values, but then I don't have the place_id.
How can I get the minimum values of the place_id within in the group of points, being the same?
I've looked and played around with variations round but I can't figure out how to do this. Any suggestions??  
 
greetz sjors

Re: howto?

From
Bruno Wolff III
Date:
On Mon, Mar 17, 2003 at 18:09:31 +0100, Sjors <jkwast@freeler.nl> wrote:
> Hi,
> 
> Being not able to grasp in full sql, I've decided to call on the list. I've got a table with points, place_id and
distancebetween points and place_id. For each point there can be more place_id's but I want to selesct the one with the
minimumdistance. Now I can group by the points and select the min(distance) but I can't do this if place_id is
involved.I used a statment like this:
 
> 
> Select points, place_id, distance from table
> group by points, place_id, distance
> having count(*)>1 and distance=(select min(distance) from table

How about:
select distinct on (points) points, place_id, distance from table
order by points, distance;