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?