I am pretty good with the sql commands but I am not that good with exotic select commands either.
Thanks to all for your suggestions. I am going to play with them to see which one works the best. Dan Goldberg From: William Stacy Sent: Wednesday, September 28, 2011 9:33 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: select min command 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 Sent: Tuesday, September 27, 2011 2:21 PM To: RBASE-L Mailing List 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
