|Subject||RE: [firebird-support] Re: INSERT INTO...SELECT...ORDER BY|
> hmmm...let me try to explain what I'm going to to (sorry, my englishIt goes something like this.
> 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
> 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?
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
SELECT ID, MANUFACTUERNAME FROM TABLENAME ORDER BY MANUFACTURERNAME
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.