Terry, I think this is what you are looking for:
'**This statement creates a list with the RTE_NO and the highest RTE_LET in ZONE 1 Select Rte_No, MAX(ASC(Rte_Let)) "maxGen" from RZStop where Zone = 1 into FIRST__PART NoSelect group by rte_no '**This statement selects the routes listed in the list FIRST__PART Select Rte_No, Rte_Let, Directn, Stop, SeqNo, Time, RunOrder from RZStop where Rte_No + "#" + Rte_Let In (Select Rte_No + "#" + maxGen From FIRST__PART) order by Rte_No, Stop, Rte_Let, Directn, SeqNo HTH, Peter Horsbøll Møller GIS Developer, MTM Geographical Information & IT COWI A/S Odensevej 95 DK-5260 Odense S. Denmark Tel +45 6311 4900 Direct +45 6311 4908 Mob +45 5156 1045 Fax +45 6311 4949 E-mail [EMAIL PROTECTED] http://www.cowi.dk/gis -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Terry McDonnell Sent: Monday, December 19, 2005 7:23 PM To: Uffe Kousgaard; [EMAIL PROTECTED] Subject: RE: [MI-L] How to get the recs with the "highest" letter code. Uffe Thanks for the reply. I have to max( asc( rte_let)) otherwise I get a data type mismatch error. i.e. MAX() only works on numeric fields. I've resigned myself to the fact that I need more steps, but this isn't helped by the fact that SQL doesn't work on intermediate cursors - only on base tables. So it looks like back to boring do loops! :-( Terry -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Uffe Kousgaard Sent: 19 December 2005 17:46 To: Terry McDonnell Subject: Re: [MI-L] How to get the recs with the "highest" letter code. Terry, I wouldn't be surprised if MI can't handle your SQL below and I don't really know, what to expect from the result. You have something like select max(.....) "maxgen" group by maxgen. In normal SQL it would go like this: select field1, max(field2) group by field1 You also select fields, which are not part of the group by. That is allowed in MI, but not in many other SQL's. I would recode the SQL, probably in more steps to be sure to get what you are after. Kind regards Uffe Kousgaard www.routeware.dk ----- Original Message ----- From: "Terry McDonnell" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; "[EMAIL PROTECTED]" <[email protected]> Sent: Monday, December 19, 2005 5:46 PM Subject: RE: [MI-L] How to get the recs with the "highest" letter code. > Hi Lars > > Thanks for the further reply. > > Erm, there's nothing normal about the cut-down version of SQL in MB. > I've been used to (and so spoilt by) the much richer version in VFP and MB's doesn't even come close. As such I've rarely used "group by" but I had a go, as below: > > Select Rte_No, MAX( ASC( Rte_Let)) "maxGen", Directn, Stop, SeqNo, > Time, RunOrder > from RZStop > where Zone = 1 into csrZonesServices NoSelect > group by rte_no, maxGen, stop > order by Rte_No, Stop, maxGen, Directn, SeqNo > > It's nearly right - gets all the data in the exact order I want, but I > get ALL gen's of those routes that have more than one. > Can't figure this one out. > > Cheers > > Terry > > ________________________________ > > From: Lars V. Nielsen (GisPro) [mailto:[EMAIL PROTECTED] > Sent: 16 December 2005 20:01 > To: Terry McDonnell > Subject: Re: [MI-L] How to get the recs with the "highest" letter > code. > > > Hi Terry, > > You need to group your query to get the highest number for each route. Normal SQL stuff ;-) > > > Best regards / Med venlig hilsen > Lars Nielsen > GisPro > > > > Terry McDonnell wrote: > > Hi Ian, Lars and Søren > > Thank you very much for your replies, all rather similar. > > Well this gets me started only maybe I didn't make myself clear. I > said: > > "Can't figure out how to dynamically select the latest gen for each of > a bunch of disparate routes that I might want in the list. (e.g. list all the routes that stop at this stop, or pass through this polygonal zone)" > > Max(Asc(gen)) WILL return the highest gen (although I then need to; > Browse CHR$( Max(Asc(gen))) "Generation" > To get it back to a meaningful column name and in the "A" to "Z" form. > > Trouble is, it only returns ONE record, the one with the highest Gen. > I DID simplify the task for purposes of this request in that I'm actually interested in the link table between the Zones and Routes: a m-to-m reln. With the arrival times of each route in the zone: RZStop > I need to return all the details of: > all the RZStop recs for the highest Gen of all the routes > all times when each route visits the zone ( there are several repetitions of each route during the day) > > How do I do that? > > E.g. Assuming I want all routes at zone 20: > > Zone Rte_No Gen Direction Time > 20 X100 A O 0800 > 20 X100 A O 0810 > 20 X100 A O 0815 > 20 X100 A O 0822 > ... Other zones for this same route and gen > 20 X100 B O 0800 > 20 X100 B O 0810 > 20 X100 B O 0815 > 20 X100 B O 0818 > 20 X100 B O 0822 > ... Other zones for this same route and gen > 20 A200 A O 0800 > 20 A200 A O 0810 > 20 A200 A O 0820 > ... Other zones for this same route and gen > 20 A200 B O 0822 > 20 A200 B O 0825 > 20 A200 B O 0830 > > Should return > > Zone Rte_No Gen Direction Time > 20 X100 B O 0800 > 20 X100 B O 0810 > 20 X100 B O 0815 > 20 X100 B O 0818 > 20 X100 B O 0822 > ---------------------------------------- > 20 A200 B O 0822 > 20 A200 B O 0825 > 20 A200 B O 0830 > > Essentially like a cut-down timetable on a bus shelter, only with just > the one stop/zone. > I've got the feeling that I'm going to have to do this in several > passes, but one SQL pass takes ages in itself. > Another proble is that I need to sort the list on 6 or 7 fields, but > MB only allows 5, but I can fix that by pre-sorting the table into a cursor. > > Unless anyone has any bright ideas! ? > > Thanks again > > Terry > > -----Original Message----- > > Terry > > Try Max(Asc(gen)). > > Asc will return the ASCII code of the letter. > > Cheers Ian > > -----Original Message----- > > Hi Foilks > > I have a table of bus routes, where each route has a route number and > a generation key. e.g. the original route has a gen of "A". When the route is revised, the next gen is "B" and becomes the current operating route, and so on. The previous generations stay on file though. > > Now when we want to get route info, via SQL, into, say, a browse, > we're only interested in the latest gen. But not ALL routes will be at the same gen; some may be still gen A while others can be anything up to "Z". I tried the MAX( gen) function in the SQL but MI wouldn't have that. So I'm stuffed. Can't figure out how to dynamically select the latest gen for each of a bunch of disparate routes that I might want in the list. (e.g. list all the routes that stop at this stop, or pass through this polygonal zone) > > Any ideas/solutions? > > 'ppreciate it > > Terry McDonnell _______________________________________________ MapInfo-L mailing list [email protected] http://www.directionsmag.com/mailman/listinfo/mapinfo-l _______________________________________________ MapInfo-L mailing list [email protected] http://www.directionsmag.com/mailman/listinfo/mapinfo-l _______________________________________________ MapInfo-L mailing list [email protected] http://www.directionsmag.com/mailman/listinfo/mapinfo-l
