Or without subquery:

SELECT
U1.INCIDENT, U1.UNIT, U1.ARRIVAL_TIME,
COUNT(*) AS ARRIVAL_ORDER
FROM UNIT_TABLE U1
JOIN UNIT_TABLE U2 ON U2.INCIDENT = U1.INCIDENT
AND U2.ARRIVAL_TIME <= U1.ARRIVAL_TIME
GROUP BY U1.INCIDENT, U1.UNIT, U1.ARRIVAL_TIME
ORDER BY U1.INCIDENT, U1.ARRIVAL_TIME

Regards

Laszlo

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> No, unfortunately CTE's were introduced in Firebird 2.1 (or possibly 2.0), so my suggestion will not work with Firebird 1.5. Like you, I'm still generally using Fb 1.5, although a more updated version (with less bugs) than 1.5.0 and CTE's like the one I wrote yesterday is amongst the things I miss.
>
> With Firebird 1.5 it is still doable in a query, but I suppose it will be more time consuming and less flexible if you have some additional requirements:
>
> SELECT U1.INCIDENT, U1.UNIT, U1.ARRIVAL_TIME,
> (SELECT COUNT(*)
> FROM UNIT_TABLE U2
> WHERE U2.INCIDENT = U1.INCIDENT
> AND U2.ARRIVAL_TIME <= U1.ARRIVAL_TIME) as ARRIVAL_ORDER
> FROM UNIT_TABLE U1
> ORDER BY U1.INCIDENT, U1.ARRIVAL_TIME
>
> HTH,
> Set
>
> -----Original Message-----
> From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of legacydrum
> Sent: 9. april 2010 06:12
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Re: SQL query, is this possible?
>
> 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
>