oops, I meant a simple 'select zipcode into tzip from' On Wed, Sep 28, 2011 at 9:07 AM, William Stacy <[email protected]>wrote:
> 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 > > > > -- William Stacy, O.D. Please visit my website by clicking on : http://www.folsomeye.net
