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 ###################################################################### This e-mail message has been scanned and cleared by MailMarshal ###################################################################### ###################################################################### Attention: This e-mail message and accompanying data may contain information that is confidential and subject to legal privilege. Any information provided is given in good faith. However unless specifically stated to the contrary, Napier City Council accepts no liability for the content of this e-mail or for the consequences of any action taken on the basis of the information provided, unless that information is subsequently confirmed in writing. If you are not the intended recipient, you are notified that any use, dissemination, distribution or copying of this message or data is prohibited. If you received this e-mail message in error, please notify us immediately and erase all copies of this message and attachments. Thank you. _______________________________________________ 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
