>>> I wish to display the ID (of all time points = 0). I can handle this programatically but rather would do in SQL.
>>> The following query returns the time points
>>>
>>> SELECT ID as VAL
>>> FROM x
>>> WHERE "TIME" = 0
>>> order by id
>>>
>>> The values are
>>> 87
>>> 117
>>> 120
>>>
>>> Now, is there a function in FB that will return me an ID (sequential, starting at 1 and increments of 1) for each of the records like so?
>>>
>>> ID VAL
>>> 1 87
>>> 2 117
>>> 3 120

>> Although it might be possible with a mix of RDB$SET_CONTEXT,
>> RDB$GET_CONTEXT, it's rather easy with a selectable stored procedure.

>Thank you Thomas.
>
>I am not sure I am following. Could you possibly provide some more information on how this could work?

This is to a certain extent doable in a select statement, but the select statement would not be simple. What Thomas said, was that it was simple if you wrote a stored procedure to do it (google or look at the Release Notes to see examples). Though you do not have to use a stored procedure with newer Firebird versions, you can achieve much of the same by using EXECUTE BLOCK:

EXECUTE BLOCK RETURNS (ID INTEGER, VAL INTEGER)
AS
BEGIN
ID = 0;
FOR SELECT ID
FROM x
WHERE "TIME" = 0
ORDER BY ID
INTO :VAL
DO
BEGIN
ID = ID + 1;
SUSPEND;
END
END

I don't know how the RDB$SET_CONTEXT and RDB$GET_CONTEXT that Thomas suggested works, but I suspect the above EXECUTE BLOCK is considerably simpler than using these. What I do know, is that you can achieve a similar result to EXECUTE BLOCK by using a recursive CTE, but that has some drawbacks in that it is complex, resource consuming and only works if you're selecting a limited number of records (I'm not sure whether it is 1000 or 32000).

HTH,
Set