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 Thanks in advance!
View this message in context:
Doubt about join clause Sent from the
PostgreSQL - general mailing list archive at Nabble.com.