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
