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 2.5 CF099 94000 87.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
