Solved

SQL Server 2005 Update Stored Procedure for Ranking Field

Posted on 2007-11-15
5
248 Views
Last Modified: 2013-11-26
I have a table with a ranking field, so they can have a custom order/rank when being displayed.

e.g.

RANK TABLE
ID | NAME | RANK
1 | Carl | 1
2 | Jon | 2
3 | Fred | 3
4 | Leeroy | 4
5 | Sid | 5

What I need is a stored procedure which takes one parameter which is the ID of the record which is to have its rank moved up...

i.e. You want to move Leeroy up one so you call this procedure with the parameter @ID=4 and it does the following:

1) Finds the record with the next highest rank, i.e. 3 | Fred | 3
Note: I dont want it to just add or minus the rank just in case a record has been deleted, so in theory there could be gaps in the rank numbers and no record with a rank of 3 so it needs to swap with the record with a rank of 2
2) Checks if its already the highest rank which it isnt anyway so continues
3) Swaps the ranks so Leeroy is now 3 and Fred 4 so the table now looks like this when sorted by rank:

RANK TABLE
ID | NAME | RANK
1 | Carl | 1
2 | Jon | 2
4 | Leeroy | 3
3 | Fred | 4
5 | Sid | 5

Im assuming doing this in a stored procedure is a good way of doing it but I dont have much t-SQL experience, if people think im heading the wrong direction and this should be coded in the ASP.NET pages then let me know also but I think a stored procedure would be more tidy.
0
Comment
Question by:createnetwork
  • 3
  • 2
5 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
Comment Utility
please try this:
CREATE PROCEDURE dbo.MoveUpRank ( @ID INT )

AS

BEGIN

  DECLARE @rank INT

  SELECT @rank = rank - 1 FROM yourtable WHERE ID = @ID
 

  IF @rank > 1 

  UPDATE yourtable 

      SET rank = CASE WHEN ID = @ID THEN @rank ELSE rank + 1 END

  WHERE ID = @ID 

         OR ID = ( select ID FROM yourtable WHERE rank = @rank )
 

END

Open in new window

0
 
LVL 2

Author Comment

by:createnetwork
Comment Utility
Thanks for quick answer and looks good although not tested yet...

But will that work if I have deleted a record and some ranking numbers are missing?

i.e. Fred was deleted then you wanted to move Leeroy up and swap rank numbers with Jon?

RANK TABLE
ID | NAME | RANK
1 | Carl | 1
2 | Jon | 2
4 | Leeroy | 4
5 | Sid | 5

Or would I be better keeping the rank field always correct and if a record is deleted it adds 1 to all records below it?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>i.e. Fred was deleted
you should simply ensure that when a record is deleted, it "moves up" the other records:



CREATE PROCEDURE dbo.DeleteItem ( @ID INT )

AS

BEGIN

  DECLARE @rank INT

  SELECT @rank = rank FROM yourtable WHERE ID = @ID

 

  DELETE yourtable WHERE ID = @ID

  

  UPDATE yourtable SET RANK = RANK - 1

  WHERE RANK > @rank

END

Open in new window

0
 
LVL 2

Author Comment

by:createnetwork
Comment Utility
Fantastic, great solution and acepted.

On a slightly different note, do you know any books you would recommend on learning how to programme T-SQL better? id been looking at this:

http://www.amazon.co.uk/Beginning-Server-small-Programming-Programmer/dp/0764584332/

Is that going to help me learn things like you just suggested? Thanks again.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
I did not learn T-SQL, PL-SQL, etc programming.
I learned, in school, programming methodology, with the first practice in Borland Pascal...
once one knows the basic concepts of how to analyze correctly, and the first think about the entire problems, you can write code very quickly in any language.
of course, some practice is needed in the relevant language to learn the syntax and it's limitations, but the basics are always the same:
* data types, including arrays, collections, self-written classes.
* looping code
* conditional code
* procedural code (ie parameter passing)
* error handling
* and most important: using "abstraction" ie boxing the problem: divide an conquer.
   => don't try to solve all the problems at once. divide the whole into several steps (where you might have several ways), and solve each step individually. if you have several ways, think about the limitations, and find out what way will be most efficient and/or better to use.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now