My mistake. It should have been CF099. Dan Goldberg From: A.G. IJntema Sent: Wednesday, September 28, 2011 7:23 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: select min command Hi Dan,
First, what I don’t uderstand in your example is the result. dealernum zipcode --------- ------- CF001 93535 CF001 94000 The second row is not the minimum. It seems to me that CF099 is the minimum distance with the second zipcode. What I miss in your table is an unique identifier. In the case a UID is available you could create the result table in two steps. First select all UID’s in a table and then add the necessary columns to this just created table based upon this UID Hope this helps Tony From: [email protected] [mailto:[email protected]] On Behalf Of Dan Goldberg Sent: woensdag 28 september 2011 15:51 To: RBASE-L Mailing List Subject: [RBASE-L] - RE: select min command 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
