In response to jc_mich <juan.mich...@paasel.com>: > > 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!
Something like this should work, (assuming I understand your tables): SELECT clients.id, stores.id, min(sqrt(power(clients.x-stores.x)+power(clients.y-stores.y)) FROM clients, stores GROUP BY clients.id, stores.id; -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
