2009/4/20 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 >
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.rembialkow...@gmail.com http://filip.rembialkowski.net/
