Subject RE: [firebird-support] Re: INSERT INTO...SELECT...ORDER BY
Author Alan McDonald
> hmmm...let me try to explain what I'm going to to (sorry, my english
> is not very good):
> I have an application, that builds a tree-structure based on a db-
> table, connected via ODBC. Unfortunately I can't change this
> application.
> This app builds the tree ordered by a keynumber (ID). Because I want
> this tree to be ordered by the manufacturer-names, the ID must be
> assigned to the names ordered same way - I hope You see what I mean.
> So I built a generator to generate the ID and a "on-insert"-trigger
> into the manufacturer-table.
> Before starting the app, I clear the table, set the generator to 0
> and insert the ordered records from another table, which contains the
> articles of several
> manufactureres . As result i get the ID ordered
> by names.
> Do You know a better way to solve this task?

It goes something like this.
Store your records as you do. Leave the PK for each record, as is, forever.
Never reset the generator.
Load your tree with this query

As you load the tree on client side, concatentate an incrementing number
adjacent to the manufacturer's name value to give you this fictional number,
which will never be persistent anyway.
You will still have the real PK for the record which most tree components
also provide a way of storing.