The problem is not the formula, the problem is the join. I've summarized it because is a large formula and I've got right results Here is the complete code code, stores and clients have a longitude and latitude, and ponderator is a demand variable: SELECT distances.client_id, min(distances.dist) FROM( SELECT stores.id AS store_id, clients.id AS client_id, 6378*stores.ponderator*acos(cos((90-clients.lat)*(pi()/180))*cos((90-stores.lat)*(pi()/180))+sin((90-clients.lat)*(pi()/180))*sin((90-stores.lat)*(pi()/180))*cos((clients.lon-stores.lon)*(pi()/180))) AS dist FROM stores, clients WHERE 1=1 ORDER BY stores.id, dist) AS distances GROUP BY distances.client_id; I only want to assign to each store a client in function of the minimum distance between them. Thanks Best regards Filip Rembiałkowski-3 wrote: > > 2009/4/20 jc_mich <juan.michaca@xxxxxxxxxx> > >> >> 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/ > > -- View this message in context: http://www.nabble.com/Doubt-about-join-queries-tp23142980p23159394.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
