Thanks for the help. I forgot to thank you for the zipcode distance formula you posted. That is related to this and I am using it successfully!
Dan Goldberg From: Bill Downall Sent: Wednesday, September 28, 2011 7:27 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: select min command Dan, How about: CREATE VIEW MinDealerDistanceView + (ZipCode, MinDistance) + AS SELECT ZipCode, MIN (Distance) + FROM DealerDistanceTable + GROUP BY ZipCode SELECT DealerNum, MinDistance + FROM DelearDistanceTable t1, MinDealerDistanceView v2 + WHERE ZipCode = .vZipCode + AND t1.Distance = v2.MinDistance You may get more than one row in the result, if there are ties for the minimum distance. Bill On Wed, Sep 28, 2011 at 9: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
