Solved

SQL Server 2005 Update Stored Procedure for Ranking Field

Posted on 2007-11-15
5
254 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
ID: 20288260
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
ID: 20288296
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]
ID: 20288303
>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
ID: 20288315
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]
ID: 20288343
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

770 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