I am drawing a complete blank on how to do this in 7.0.4:

I have 1 table called "Meters" storing the meter ID's of dispensers at
gas stations, along with an identifier (ENtitykey) which identifies
the station. Another table called "MeterStatus" stores the status
(idle, pumping, etc) of the individual meters, with a fkey into the
meters table, along with a DateTime field called DateGenerated which
stores the date and time the status changed. There are several meters
at each station.

I need to come up with an sql statement which will give me the latest
status for each meter at a given station (specified by its entitykey).
I have no problem with the statement to get *all* the meter status
entries along with the station ID information, but cannot figure out
how to get the *latest* meter status (by dategenerated). If I could
do an ORDER BY in a sub-select I don't think it would be difficult,
but that's not allowed in 7.x.

Maybe it's just because it's late in the day, but I'm drawing a blank
on how to get this going.

Thanks for any suggestions!
--
Dave Kerber
Fight spam: remove the ns_ from the return address before replying!

REAL programmers write self-modifying code.

Select * from Meters M1
where
entityKey = :stationNumber and
dategenerated =
(Select max(dategenerated)
from Meters M2
where M2.meterID = M1.meterID ) ;

--
Paul Horan[TeamSybase]


"David Kerber" <ns_dkerber (AT) ns_wraenviro (DOT) com> wrote