Thanks for your response! I really appreciate it. We will give that a try. Do you know if this function will work with Firebird 1.5.0.4306?

Sincerely,

Brad...

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> Yes, it is Brad - at least if you use a reasonably new Firebird version and don't have too many records for each incident. In doing so, you need is a recursive CTE, something like (not tested):
>
> WITH RECURSIVE MyCTE(INCIDENT, UNIT, ARRIVAL_TIME, ARRIVAL_ORDER) as
> (SELECT U1.INCIDENT, U1.UNIT, U1.ARRIVAL_TIME, CAST(1 AS INTEGER)
> FROM UNIT_TABLE U1
> LEFT JOIN UNIT_TABLE U2
> ON U1.INCIDENT = U2.INCIDENT
> AND U1.ARRIVAL_TIME > U2.ARRIVAL_TIME
> WHERE U2.INCIDENT IS NULL
> UNION ALL
> SELECT U1.INCIDENT, U1.UNIT, U1.ARRIVAL_TIME, M1.ARRIVAL_ORDER+1
> FROM MyCTE M1
> JOIN UNIT_TABLE U1
> ON M1.INCIDENT = U1.INCIDENT
> AND M1.ARRIVAL_TIME < U1.ARRIVAL_TIME
> LEFT JOIN UNIT_TABLE U2
> ON U1.INCIDENT = U2.INCIDENT
> AND U1.ARRIVAL_TIME > U2.ARRIVAL_TIME
> AND M1.ARRIVAL_TIME < U2.ARRIVAL_TIME
> WHERE U2.INCIDENT IS NULL)
> SELECT <whatever>
> FROM INCIDENT_TABLE I
> JOIN MyCTE M ON I.INCIDENT = M.INCIDENT
> ORDER BY M.INCIDENT, M.ARRIVAL_ORDER
>
> This may look complicated at first (feel free to replace the LEFT JOIN and WHERE clauses with WHERE NOT EXISTS ... - you should get the same result) and a stored procedure will look simpler if you're not used to recursive CTEs, but I think CTEs are reasonably well explained in the release notes of Firebird 2.1.
>
> HTH,
> Set
>
> -----Original Message-----
> From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of legacydrum
> Sent: 7. april 2010 23:20
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] SQL query, is this possible?
>
> In a SQL query in a Firebird database where I have a master-detail table join (Incidents-Units), I would like to create a row number field for the detail records that resets for each mater table record. The following is a sample output I am looking to create: Is it possible to perform this query in Firebird? If so, how would I go about creating it?
>
> Thanks,
>
> Brad
>
> INCIDENT UNIT ARRIVAL_TIME ARRIVAL_ORDER
>
> ----------------------------------------------------------------------
>
> 0013810 E1 2010-04-01 17:52:14 1
>
> 0013810 SQ1 2010-04-01 17:53:01 2
>
> 0013810 E2 2010-04-01 17:53:17 3
>
> 0013810 T1 2010-04-01 17:54:26 4
>
> 0013813 SQ3 2010-04-01 21:06:09 1
>
> 0013813 E3 2010-04-01 21:06:56 2
>
> 0013813 BC3 2010-04-01 21:08:32 3
>