The outer query gives me the last sighting for a rail car.  Now I
have the very last sighting, however within the last sighting results
there will be multiple records with that clm_id(sighting)   with
that date but different sighting codes for different transactions
within the sighting.  I want to filter the max sighting_code from the
records that are the last sighting to get the greatest code letters a-
z.

I don't know how to relate the inner query to the outer query so I put
the c.clm_id = c.clm_id  and c.sighting_date which gives me more than
one record.
The outer query gets the last sighting for a car and then
it has to compare those records with themselves to get the max
sighting code.

I'm not sure if I have the relationship of the inner query to the
outer query correct to get the two filters for max sighting date and
max sighting code?

tia,

SELECT c.l_e, c.car_mark, c.car_number, c.location_city,
c.location_state, MAX( c.sighting_date ) , e.classification,
c.railroad, s.city AS origin_city, s.state AS origin_state,
c.destination_city, c.destination_state, t.eta, c.sighting_code
FROM INTERNAL_REMARK r, INTERNAL_CAR_LEASE cl, INTERNAL_CODES e,
INTERNAL_CLM AS c
LEFT JOIN INTERNAL_TRIPS AS t ON c.trip_id = t.trip_id
JOIN INTERNAL_SPLC AS s ON t.origin_splc_id = s.splc_id
WHERE cl.lease_id =74
AND cl.remark_id = r.remark_id
AND r.car_mark = c.car_mark
AND c.sighting_code = e.code
AND c.car_number = r.car_number
AND c.sighting_code > (

SELECT c.sighting_code
FROM INTERNAL_CLM
WHERE c.clm_Id = c.clm_id
AND c.sighting_date = c.sighting_date
)
GROUP BY c.car_mark, c.car_number, c.l_e, c.location_city,
c.location_state, c.sighting_date, c.sighting_code, e.classification,
c.railroad, s.city, s.state, c.destination_city, c.destination_state,
t.eta