Autonumber as rank field ?

[p27br]

Hello,

I am having trouble with a program I have written:
I create a table with an sql statement which has an order by clause on a numeric field. This way I am sure the records are sorted when inserted into the new table. I then use a DDL statement to add an autonumber field which will be used to rank the records "ALTER TABLE ADD rank AUTOINCREMENT"

So the result should be :

----------------------
item amount rank
----------------------
A 23 1
B 34 2
C 43 3
....

in some rare cases, the ranking isn't correct so I was wondering if I can trust this method of ranking or if there would be a better way.

 

[tonyflavell]

Hi there

Surely your main sort should be on "item" and a subsort on "amount"?

Regards

Tony

 

[p27br]

no, why ?

 

[tonyflavell]

Hi there

Sorry, maybe I misread your original question. Do you mean that "item" is grouped, with "amount" sorted?

Regards

Tony

 

[p27br]

yes item is unique.

 

[tonyflavell]

in some rare cases, the ranking isn't correct

So what happens?

Tony

 

[p27br]

well for example the top ten items get ranked from 1 to 10 instead of being ranked 91 to 100 (if there were 100 items), as if the autonumbering went random

 

[tonyflavell]

The only thing I can think is that the autoincrement is reset to the last record + 1 if the database is compacted. Could it be that records are removed from the table, which is then compacted, thereby changing the stored incremental number.

Regards

Tony

 

[p27br]

thanks for your thoughts,

I have resorted to another solution which involves an ordered recordset and an incremental counter.

 

[tonyflavell]

As long as it works - what the hell!!

Regards

Tony