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
