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.

There's am intermediate table, RZStop, that links routes to stops and the zones they're in, m-to-m

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". 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.

There may be several "sequences" of stops for each route/gen. i.e. the bus will call at a stop/zone several times in the day, once for each journey sequence.

e.g. list all the arrival times (in chron order), for all the stops, for all the routes that pass through this polygonal zone

E.g. Assuming I want all routes at zone 20:

Zone Rte_No	Gen	Direction	         Time  SeqNo
... Stops for sequence 1 ...
20   X100		A	O		0800      1 <- stop/zone we're interedted in
... Rest of the stops for sequence 1 ....
... Stops for sequence 2
20   X100		A	O		0810      2 <- stop/zone we're interedted in
... Rest of the stops for sequence 2 ....
... etc.
20   X100		A	O		0815      3
20   X100		A	O		0822      4
...  Other zones for this same route and gen
20   X100		B	O		0800      1
20   X100		B	O		0810      2
20   X100		B	O		0815      3
20   X100		B	O		0818      4
20   X100		B	O		0822      5
...  Other zones for this same route and gen
20   A200		A	O		0800      1
20   A200		A	O		0810      2
20   A200		A	O		0820      3
...  Other zones for this same route and gen
20   A200		B	O		0822      1
20   A200		B	O		0825      2
20   A200		B	O		0830      3

Should return

Zone Rte_No	Gen	Direction 	Time
20   X100		B	O		0800      1
20   X100		B	O		0810      2
20   X100		B	O		0815      3
20   X100		B	O		0818      4
20   X100		B	O		0822      5
----------------------------------------
20   A200		B	O		0822      1
20   A200		B	O		0825      2
20   A200		B	O		0830      3

Essentially like a cut-down timetable on a bus shelter, only with just the one stop/zone.

I've tried lots of permutations of the below:

Select Rte_No, MAX( gen) as maxGen, Directn, Stop, SeqNo, Time, RunOrder ;
 from RZStop ;
  into CURSOR csrZonesServices ;
  order by Rte_No, gen, Stop,  Directn, SeqNo ;
  GROUP BY Rte_No, gen, Directn, Stop, SeqNo

but I keep getting all gen's of those routes that have more than one.

Can you help please

'ppreciate it

Terry