Using Loops and Getting Record Number info

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

SQL Server Development (2000)

Using Loops and Getting Record Number info

Author

Topic

hoyaabanks
Starting Member

1 Post

Posted - 2006-05-06 : 17:43:33

I have a data table that sort of looks like this

ID Name Priority
-------------------------
31 Cars 26
43 Motorcycles 26
55 Boats 27
56 Sail Boats 28
2 Ducks 34
44 Skateboards 44

The items are order on a web page based on their priority. I want to write a Stored Procedure to clean up the priority values every now and agian, as records are added and removed etc;....

Once you run this procedure the same table should look like this

ID Name Priority
-------------------------
31 Cars 1
43 Motorcycles 2
55 Boats 3
56 Sail Boats 4
2 Ducks 5
44 Skateboards 6

So here is my basic Algorythem:

a. Select Records from Transportation Order By Prioirty
b. Set the priority value to the order of the select statement starting with 1

I am not sure how to accomplish the second part. How would I write that. Would I use a loop? How? How do you get the record number value?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-06 : 21:51:48

update tbl
set Priority = (select count(*) from tbl t2 where t2.Priority < t1.Priority or (t2.Priority = t1.Priority and t2.ID , t1.ID)) + 1
from tbl t1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-06 : 23:41:53

minor typo error in nr's code


update tbl
set Priority = (select count(*) from tbl t2 where t2.Priority < t1.Priority or (t2.Priority = t1.Priority and t2.ID < t1.ID)) + 1
from tbl t1

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-05-07 : 17:12:24

Better use cursor, it is more efficient. There is also faster non-curosor solution, see http://www.sql-server-performance.com/mm_cursor_friendly_problem.asp

One solution mentioned there (with select top ... order by) doesn't work with sql server 2005.

Instead of adding total, just add 1 for each row.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-07 : 17:31:46

Given that this is for display on a web page I doubt if performance is an issue and the overhead of instantiating the cursor might be significant.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-05-07 : 17:42:42

If the performance is an issue, then (as usual) it is good idea to test different implementations and find which one fits the best.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources