Question for a sp for get order number? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Question for a sp for get order number?

Author

Topic

mshen
Starting Member

8 Posts

Posted - 2004-07-27 : 17:56:05

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

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-07-27 : 18:14:00

I'd use:

UPDATE name_table
   SET sort_order = (SELECT Count(*)
      FROM name_table AS b
      WHERE  b.id <= name_table.id)

-PatP

mshen
Starting Member

8 Posts

Posted - 2004-07-28 : 09:20:02

Thanks Pat,it is good

Do you know know to do the same thing if the sort_order'order is not same with id'order.it like this before we use the sp.

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

Thanks for help.

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-07-28 : 10:41:48

Use the same UPDATE statement.

-PatP Itinerant Curmudgeon

mshen
Starting Member

8 Posts

Posted - 2004-07-28 : 11:26:50

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources