Set,
Thanks for your reply. That seems to work for the one table.
Hoever, after looking at it further that is the detail table and not
the master. I need to add the SQL code you mentioned below to the
following view. Do you have any ideas on how to do this?

CREATE VIEW SECONDAPPARATUSARRIVAL(
ID,
FDID,
LOCATION_ID,
INCIDENTNO,
INCIDENTTYPE,
EXPOSURE,
INSERVICEDATE,
ENROUTEDATE,
CLEARDATE,
APPARATUSID,
ARRIVALDATE,
DIFFHRS,
DIFFMIN)

AS
SELECT
H.ID,
H.FDID,
H.LOCATION_ID,
H.INCIDENTNO,
H.INCIDENTTYPE,
H.EXPOSURE,
T.INSERVICEDATE,
T.ENROUTEDATE,
T.CLEARDATE,
T.APPARATUSID,
T.ARRIVALDATE,
CAST(24*(T.ARRIVALDATE-T.ENROUTEDATE) AS INTEGER),
CAST((((T.ARRIVALDATE-T.ENROUTEDATE)*24)-CAST((T.ARRIVALDATE-
T.ENROUTEDATE)*24 AS INTEGER)) * 60 AS INTEGER)

FROM FIRE_INCIDENT_BASE_INFORMATION H RIGHT JOIN APPARATUSRESPONSE T
ON H.ID = T.ID;

Thanks again for your help,

Brad


--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
<svein.erling.tysvaer@...> wrote:

>
> Hi Brad!
>
> Another way to think of your problem, is that there should exist

one,

> but but not two records with a prior datetime. Assuming there are

no

> duplicate datetimes, you could then do
>
> SELECT M1.ID, M1.MYDATETIME
> FROM MYTABLE M1
> WHERE EXISTS(
> SELECT * FROM MYTABLE M2
> WHERE M2.ID = M1.ID
> AND M2.MYDATETIME < M1.MYDATETIME)
> AND NOT EXISTS(
> SELECT * FROM MYTABLE M3
> JOIN MYTABLE M4 ON M3.ID = M4.ID
> WHERE M3.ID = M1.ID
> AND M3.MYDATETIME < M1.MYDATETIME
> AND M4.MYDATETIME < M3.MYDATETIME)
>
> HTH,
> Set
>
> legacydrum wrote:
> > I am in need of some SQL help. I have a detailed table with

multiple

> > records for each ID with date/times as listed below.
> >
> > ID DATETIME
> > 2 1/14/2007 9:18:26 AM
> > 2 1/14/2007 9:20:10 AM
> > 2 1/14/2007 9:21:17 AM
> > 2 1/14/2007 9:23:05 AM
> > 3 1/20/2007 12:07:15 AM
> > 3 1/20/2007 12:09:19 AM
> > 3 1/20/2007 12:15:45 AM
> > 3 1/20/2007 12:22:50 AM
> >
> > I need to be able to query the data and return the second

greatest

> > time for each ID which would result as follows.
> >
> > ID DATETIME
> > 2 1/14/2007 9:20:10 AM
> > 3 1/20/2007 12:09:19 AM
> >
> > If it were the first or last record, I could use SELECT MAX.
> > However, I am not sure how to do this. Any idea's would be

greatly

> > appreciated.
> >
> > Sincerely,
> >
> > Brad
>