quludata wrote:

> Hi, is there a way to have a RowNumber for each row returned by a
> query?
>
> like: select RowNum(), fld1, fld2 from MyTable order by 3, 2
>
> I know this is probably not good SQL, but the 3rd party visual
> component I'm using expects a numeric ID from the dataset.
>

The simplest way, expecially if you don't care about the range (or the
numbers to be consecutive even), is something like:

CREATE GENERATOR rownum;
SELECT gen_id(rownum,1), mt.fld1, mt.fld2 FROM MyTable mt ORDER BY 3, 2

Since we aren't talking about good SQL (;-) you may even reset the
generator before running the query, provided that there's almost one
session playing with it. The following stmt will bring the generator
back to zero:

SELECT gen_id(rownum, -gen_id(rownum, 0) FROM rdb$database

Just a little more elaborated, but probably a better way, is using a
stored procedure, if you can reduce the number of different queries down
to something reasonable:

CREATE PROCEDURE CountingMyTable
RETURNS (rownum INTEGER, fld1 SomeType, fld2 SomeOtherType) AS
BEGIN
rownum = 0;
FOR SELECT mt.fld1, mt.fld2
FROM MyTable mt
ORDER BY 2,1
INTO fld1, fld2 DO
BEGIN
rownum = rownum+1;
SUSPEND;
END
END

SELECT * FROM CountingMyTable;

hth,
ciao, lele.