2009/4/20 jc_mich <juan.michaca@xxxxxxxxxx>
It would be much easier if you show actual database schema.
It is not clear what is the meaning of stores.x and stores.y variables - what do they measure. If they are just coordinates, then where are client coordinates stored?
Hello
I have a table with clients and other with stores, I want to calculate
minimum distances between stores and clients, the client name and its closer
store.
At this moment I can only get clients ids and minimum distances grouping by
client id, but when I try to join their respective store id, postgres
requires me to add store id in group clause and it throws as many rows as
the product of number clients and stores. This result is wrong, I only
expect the minimum distance for every client.
My code looks like this:
SELECT distances.client_id, min(distances.distance) FROM(
SELECT stores.id AS store_id, clients.id AS client_id,
sqrt(power(store.x)+power(store.y)) AS distance
FROM stores, clients
WHERE 1=1
ORDER BY stores.id, dist) AS distances GROUP BY distances.client_id;
Also I've tried this:
SELECT clients.id, MIN(distances.distance)
FROM stores, clients LEFT JOIN(SELECT clients.id AS client_id, stores.id,
sqrt(power(stores.x)+power(stores.y)) AS distance
FROM stores, clients
WHERE 1=1) distances
ON distances.client_id = clients.id GROUP BY clients.id
It would be much easier if you show actual database schema.
It is not clear what is the meaning of stores.x and stores.y variables - what do they measure. If they are just coordinates, then where are client coordinates stored?
--
Filip Rembiałkowski
JID,mailto:filip.rembialkowski@xxxxxxxxx
http://filip.rembialkowski.net/
