Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server 2005 Update Stored Procedure for Ranking Field

Posted on 2007-11-15
5
Medium Priority
?
263 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 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 143

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 143

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
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 course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

610 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