Help me to create a order column

Hi all,

In Visual Foxpro i create a Order column that:
replace all myOrderColumn with recno()

[myOrderColumn]
1
2
3
4
5
6
...
EOF()

Help me to create myOrderColumn - SQL Server 2000 (by code, not use indetity column)

Thank alot!
I am wondering why this is necessary.

Bear with me here. . .

Fundamental relational database theory states that 'order' should be based on some quality of a relation. Remember, each tuple (record) in a table is a relation (Some people erroneously think that relational means referential integrity, i.e. foreign keys.) If there is some determinant that implies order, that determinant must be derivable by an examination of one or more fields of the tuple, e. g. Alphabetical Last Name, First Name or Date Of Transaction, etc. . .

You do realize that a real RDBMS makes no guarantee that a record last inserted will be the last physically in the table.

What? evertime a record is deleted/inserted you are going to change the order value? recalculate the index?

Do you need it for a surrogate key? why does it need to be ordered?

I am willing to wager my yearly salary that if you need an order column, your data model is underqualified.

I am also willing to wager that FoxPro is not a Relational Database System.|||Well, the first person that replied to your question should probably dismount his high horse a bit, as it is sometimes handy to retrieve an ordinal number corresponding to the row being returned (especially when generating output for reporting purposes or whatnot). I won't question why you are doing this, but I trust you have. a. very. good. reason.

In SQL Server 2005 :-) you can use the ROW_NUMBER() function in your SELECT list, and that will do the job. In SQL Server 2000, you have no such function. You will have to either use a cursor, number your rows in code, or insert your results into a similarly formed temp-table with an identity column on it and then return your values.

-Dave|||

Dave Markle wrote:

Well, the first person that replied to your question should probably dismount his high horse a bit, as it is sometimes handy to retrieve an ordinal number corresponding to the row being returned (especially when generating output for reporting purposes or whatnot). I won't question why you are doing this, but I trust you have. a. very. good. reason.

I wasn't trying to be arrogant I am just telling the truth.

What is he trying to model?
What is the purpose of the ordinal?
How would you determine the order?
Why would one not just index on that determinant and forget about the ordinal?

In a relational system, there is never a good reason for doing this. An arbitrary sequence number has NO intrinsic value.
Oh it isn't arbitrary?
Then how is it determined?
Index on the determinant and forget the order by column!
Oh! it is a multi-field key and you don't want to carry all the fields over to a child table?
Use an identity as a surrogate key. Primary Key on the multiple values and unique on the identity. Use the identity in the child as a foreign key to the parent.
To get the parent keys in proper order:

select identityfield from my table order by primarykeyfield1, primarykeyfield2

It matters not that the identityfield might not be in numeric sequence. . It will be in the order that is intrinsic to the data domain.

I am just making guesses as to what need there is for the order column. Again, I am sure that there is no need for it.

In SQL Server 2005 :-) you can use the ROW_NUMBER() function in your SELECT list, and that will do the job. In SQL Server 2000, you have no such function. You will have to either use a cursor, number your rows in code, or insert your results into a similarly formed temp-table with an identity column on it and then return your values.

This isn't what he is asking for. ROW_NUMBER and a temp identity have no persistence from run to run. That is, the rows returned could have a different row number or identity value if the underlying data changes via inserts or deletes - no intrinsic value. Therefore it has no use as part of a foreign key or any other use that has any real meaning.