Terry, I am not sure if this technique will work, as I haven't tried it in MapInfo yet. I encounter a similar problem even in Access (selecting all the data from the record that has the maximum value of a particular field.
In the past I do a select to identify the maximum value for the field I am trying to identify, and then join that query back to the original table to identify the record tht has that value. viz. select Rte_No, MAX( ASC( Rte_Let)) "maxGen", Directn, Stop from RZStop where Zone = 1 into csrZonesServices NoSelect group by rte_no, maxGen, stop Into CurrentValues Then RZStop.Rte_No, RZStop.Rte_Let, RZStop.Directn, RZStop.Stop, RZStop.SeqNo, RZStop.Time, RZStop.RunOrder >From RZStop, CurrentValues Where RZStop.Rte_No = CurrentValues.Rte_No AND RZStop.Rte_Let = CurrentValues.MaxGen AND RZStop.Stop = CurrentValues.Stop order by RZStop.Rte_No, RZStop.Stop, RZStop.maxGen, RZStop.Directn, RZStop.SeqNo However, I am going to try the idea of selecting all the data, ordered by the fields that need to be grouped by, plus the one that I am looking to identify the latest or greatest. Then, if I do a select query on the result, grouped by the fields that need to be grouped by, the result should return the data from the first record of each group. This is assuming that if you don't specify a aggregate function in MapInfo SQL, it defaults to a first() option. Something like: Select Rte_No, Rte_Let, ASC( Rte_Let) "SortGen", Directn, Stop, SeqNo, Time, RunOrder from RZStop where Zone = 1 into csrZonesServices NoSelect order by Rte_No, SortGen Desc, Stop, Directn, SeqNo Into SortedList Select Rte_No, Rte_Let, Directn, Stop, SeqNo, Time, RunOrder from SortedList group by rte_no, stop order by Rte_No, maxGen, Stop, Directn, SeqNo Like I said, not sure if it will work, even if you could specify a first() aggregate, but it is one that I intend to explore at some point for my stuff. r > 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 > _______________________________________________ MapInfo-L mailing list [email protected] http://www.directionsmag.com/mailman/listinfo/mapinfo-l
