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