Subject Re: Running Number on Results
Author Adam
--- In, Svein Erling Tysvær
<svein.erling.tysvaer@k...> wrote:
> --- In, "Adam" wrote:
> > Well I don't think generators are particularly useful here. If two
> > people run the query at the same time, the numbers are likely to be
> > skipped or reset to 0 in the middle, and that is not really the
> > point of generators.
> >
> > I am not aware of anyway you can do it in FB 1.5 without using a
> > stored procedure. (Which is a pretty easy operation)
> If each record is unique and the result set ordered, then it it
> possible, although it will be pretty slow in many cases and could
> leave the SQL almost unreadable if many records are required to
> determine uniqueness:
> select
> (select count(*)
> from TableA T2
> where T2.field1 < T1.Field1
> or (T2.field1 = T1.field1 and T2.field2<=T1.field2) as LineNr,
> T1.field1, T1.field2
> from TableA T1
> order by T1.field1, T1.field2
> Though I would only do this if the code is generated on the fly, using
> a stored procedure is much better if the query isn't dynamically
> Set


OK, You found a place that would work .... as long of the record count
is tiny. Well done. If the query is dynamically generated, you could
always use execute statement function to do what you needed, and you
would want some indices to help.