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 <[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**** > > **** >
