Subject Re: Record numbering
Author Ali Gökçen
If flying hippopotamus AKA ora have ben supporting this for a long
time, it should be needing by serious number of users.
And shouldn't be a big arthitectural problem for FB i think.
We have ROW_COUNT context variable already and also counting effected
rows at server side, why not put it in select list?


--- In, "Adam" <s3057043@...> wrote:
> --- In, Svein Erling Tysvaer
> <svein.erling.tysvaer@> wrote:
> >
> > With a set ordered by a unique field, this can be done as
> >
> > select (select count(*) + 1 from MyTable M2 where M2.Somefield <
> > M1.Somefield) as RecNo, M1.Somefield
> > from MyTable M1
> > Order by M1.Somefield
> >
> > Though it doesn't work on any unordered dataset and doing it on a
> client
> > or through a stored procedure is a much better solution (though
> > above solution is quite funny and should work).
> LOL, ok you gave me a chuckle, very creative.
> If you are new to Firebird and don't get the joke, please don't use
> this suggestion if the table has more than 50 records, or at the
> least somefield is indexed and your where constraint limits it to no
> more than 50 records. There will be from my maths n^2 reads, where n
> is the number of records in Mytable.
> I have been following this thread and two things concern me.
> Firstly, why are we asking the database server to do this work? It
> simply a client side cursor of some sort. It is just adding
> unnecessary data to what must be sent to the client. You are going
> be transferring an additional 8 bytes per row + whatever size the
> flag is. You may slow down such a queries fetch time by 30% for no
> benefit.
> Secondly, most query components I have seen actually have such a
> property. It wouldn't even surprise me if most grid components had
> autonumber option.
> Thirdly, if yours does not, it is less than a trivial operation to
> generate it on the fly at the client end.
> Fourthly, it concerns me what you will do with this information. If
> is simply an indicator to the user that they are looking at a
> particular 'record number', that is ok, but if you intended on using
> the fact they are on record 74 in some future database interaction,
> the bad design alarm bells should be ringing. Firebird does not
> records by 'record number'.
> If you absolutely must get the server to generate it (and I really
> doubt that is the case), I liked Ivan's FB2 based solution, it was
> quite cute, but the cheapest would be a stored procedure.
> Adam