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
