There may be some exotic select command that could do it, but I don't know what it would be. I would just make a tiny cmd file that steps through all the dealers, one at a time, and selects the zip with the smallest dist. for that dealer, and inserts the result into the table, using something in the loop like 'comp vmin as min distance from tablename where dealernum=.twhatever', followed by a simple 'select tzip from tablename where dealernum=.twhatever and distance=.vmin' , followed by the insert, then looping back to the next row.
On Wed, Sep 28, 2011 at 6:50 AM, Dan Goldberg <[email protected]> wrote: > I tried it but it gives me: > > dealernum zipcode > --------- ------- > > CF001 93535 > > CF099 93535 > > CF011 93535 > > > > > > I need it to give me the nearest dealernum by zipcode like: > > > dealernum zipcode > --------- ------- > > CF001 93535 > > CF099 94000 > > > > > > Dan Goldberg > > *From:* Javier Valencia <[email protected]> > *Sent:* Tuesday, September 27, 2011 2:21 PM > *To:* RBASE-L Mailing List <[email protected]> > *Subject:* [RBASE-L] - RE: select min command > > Dan,**** > > **** > > You need the GROUP BY option:**** > > **** > > SELECT dealernum, zipcode, MIN(distance) GROUP BY dealernum, zipcode**** > > **** > > The statement above will generate the combination of dealernum and zipcode > with the minimum distance. **** > > **** > > OR**** > > **** > > SET ERROR MESSAGE 677 OFF**** > > DROP VIEW XXX**** > > SET ERROR MESSAGE 677 ON**** > > **** > > CREATE TEMPORARY VIEW XXX (dealernum, zipcode, MinDistance) +**** > > AS SELECT dealernum, zipcode, MIN(distance) WHERE_CLAUSE_GOES_HERE GROUP BY > dealernum, zipcode**** > > **** > > INSER INTO your_table (dealernum, zipcode) +**** > > SELECT dealernum, zipcode FROM XXX**** > > **** > > SET ERROR MESSAGE 677 OFF**** > > DROP VIEW XXX**** > > SET ERROR MESSAGE 677 ON**** > > **** > > Javier,**** > > **** > > Javier Valencia, PE**** > > O: 913-829-0888**** > > H: 913-397-9605**** > > C: 913-915-3137**** > > **** > > *From:* [email protected] [mailto:[email protected]] *On Behalf Of *Dan > Goldberg > *Sent:* Tuesday, September 27, 2011 3:18 PM > *To:* RBASE-L Mailing List > *Subject:* [RBASE-L] - select min command**** > > **** > > I am having a brain fade and am trying to figure out how to get the minimum > distance by zipcode.**** > > **** > > I have a table with the following values**** > > **** > > dealernum zipcode distance**** > > --------- -------- --------**** > > CF001 93535 1.5**** > > CF099 93535 1.8**** > > CF011 93535 4.6**** > > CF001 94000 12.5**** > > CF099 94000 7.2**** > > CF011 94000 17.9**** > > **** > > **** > > What I need to do is store the dealernum and zipcode into a results table. > **** > > **** > > dealernum zipcode**** > > --------- -------**** > > CF001 93535**** > > CF001 94000**** > > **** > > **** > > I have played with min select function but cannot figure out the grouping > to store both the dealernum and zipcode.**** > > **** > > **** > > TIA**** > > **** > > **** > > Dan Goldberg**** > > **** > -- William Stacy, O.D. Please visit my website by clicking on : http://www.folsomeye.net
