How stored procedure to change order number?

[markshen2004]

I have a name table.the structure like this now.the sort_order is incorrect because I alraedy delete some iterms manully.

I want to update the sort order and change it from 1 to n according to the current order

id name sort_order
9 John 9
16 Mark 16
27 Tony 27
45 Jim 45


I hope the data in the table like this after I run the stored procedure.

id name sort_order
9 John 1
16 Mark 2
27 Tony 3
45 Jim 4

Please give me a idea how to write the stored procedure. and let me know if it is possible a stored procedure can do it.

Thanks a lot

 

[SQLSister]

Why not just sort on the id field?

Questions about posting. See faq183-874

 

[markshen2004]

Id is a primary key.I can not change it.

I do not remeber if there is system variable like @@*** can get current record number when you use SQL select statement to list records.

id(PK) name sort_order
9 John 9
16 Mark 16
27 Tony 27
45 Jim 45


Thanks

 

[SQLSister]

I didn;t mean to change it - which you definietely should do. Just use t to sort on, that is theorder you want anyway. THe missing numbers are irrelevant to the sort process.

Questions about posting. See faq183-874

 

[markshen2004]

I like build other column to save the sort order and I also will update the sort id when I remove a record.

Thanks

 

[philhege]

SQLSister is correct, MarkShen: if you use identity and seed/increment on your PK column, the table will always sort correctly on the PK. There's no need to maintain a sort order column when the contents of the PK already do that for you.

If, on the other hand, you want to DISPLAY the ordinal, then

(this uses the ubiquitous Northwind DB; thanks to Tibor Karazsi)

IF EXISTS (select table_name from INFORMATION_SCHEMA.columns
where table_name = '#auth')
DROP TABLE #auth

SELECT
IDENTITY(int,1,1) AS cnt
,au_lname
,au_fname
INTO #auth
FROM authors a1
ORDER BY cnt

SELECT *
FROM #auth
ORDER BY cnt
DROP TABLE #auth --or whatever you need to do here

HTH,

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....
-----------
A skeleton walks into a bar, and says "I'll have a beer and a mop.