SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2002
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Sorting a Ranking Field

    Hi Folks

    Here's something that I have been wondering about for a while and I've just never got around to delving into. I am wondering if anyone's got a nifty idea about how to manage a ranking field within the data of a table easily within a query.

    Let's say we have a super simple products table that just has three fields, like this:

    id | name | ranking
    1 | Product 1 | 1
    2 | Product 2 | 2
    3 | Product 3 | 3
    4 | Product 4 | 4
    5 | Product 5 | 5

    When we go to display the products out of our database they are ordered by our ranking field. Now the questions is, if a user wants to change, say, product four to be ranked number two out of the list, is there an easy way we can re-order the "ranking" field with a query to reflect the changes to it and all the other products?

    I'd be most interested to see any solutions to this problem.

    Cheers.
    Last edited by grahowler; Feb 20, 2005 at 21:18.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    increment your ranking numbers by 100s or 1000s

    id | name | ranking
    1 | Product 1 | 1000
    2 | Product 2 | 2000
    3 | Product 3 | 3000
    4 | Product 4 | 4000
    5 | Product 5 | 5000

    then, when you want to change product four to be number two on the list, you merely update its ranking number to be between those of one and two

    id | name | ranking
    1 | Product 1 | 1000
    2 | Product 2 | 2000
    3 | Product 3 | 3000
    4 | Product 4 | 1500
    5 | Product 5 | 5000

    yes, eventually you will reach a point where you might need to renumber the ranking numbers, but in the meantime this method is efficient and simple
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Aug 2002
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK I see your point but I have been searching for a way to actually renumber the fields.

    So, I took the time to look at this more closely and came up with this, perhaps you may find this a little inefficient but I thought I'd post it anyway for some comments.

    Code:
    -- Increase the value of the ranking
    REPLACE products SELECT 
    id, 
    name, 
    CASE WHEN ranking = 2 THEN 4 
    WHEN ranking > 4 THEN ranking 
    WHEN ranking > 2 THEN ranking - 1
    ELSE ranking 
    END AS ranking 
    FROM 
    products
    Code:
    -- Decrease the value of the ranking
    REPLACE products SELECT 
    id, 
    name, 
    CASE WHEN ranking = 4 THEN 2 
    WHEN ranking > 4 THEN ranking 
    WHEN ranking >= 2 THEN ranking + 1
    ELSE ranking 
    END AS ranking 
    FROM 
    products
    So either of those queries will increase or decrease the value of the ranking fields and all the other ranking fields relative to the one you're changing. Of course in order to fill in these queries correctly you need to know the value of the ranking field you are changing it from and the value you're changing it to.

    Now the thing is I notice that when performing this query across five rows it actually performs 10 instructions. So the record first gets deleted then inserted again. Obviously this is not going to be very efficient if you have 1,000 records across which to perform the query. I guess it's going to very rare to be doing this where you don't have a WHERE clause to restrict the rows.

    Anyway, I'd be interested to hear your thoughts.

    Thx

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that looks promising, except i don't understand how you would generalize it to re-rank any row to go between any other two rows

    i personally i mean if you wanted to move row 937 to fit between rows 9 and 10, how many rows would actually have to be updated? seems like many hundreds, in a ripple effect

    the reason i say "looks promising" is because if the ranks were numbered by 1000, and you were trying to fit row 3700 in between row 93700 and row 93800 you could re-rank if the gap was less than, say, 100, but then you'd only have to touch the immediate neighbours

    if you follow what i mean
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Aug 2002
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I know exactly what you mean...

    if you wanted to move row 937 to fit between rows 9 and 10, how many rows would actually have to be updated? seems like many hundreds, in a ripple effect
    This exactly what the problem is, apart from the fact that using the method I have posted replaces every single row in the query, even outside those it needs to affect.

    In the example you've provided you'd need to move row 937 down to row 10, then increment every row's value by one all the way up to what was 936.

    So, it's hardly perfect but it's be nice to have a solution to keep them all neat and tidy and ordered. (Perhaps I am turning into a neat/control freak, yeeks!)

    Anyway, I see your point so thanks for checking out the thread.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by grahowler
    IIn the example you've provided you'd need to move row 937 down to row 10, then increment every row's value by one all the way up to what was 936.
    which, as i hope you'll see, is not necessary if you simply numbered them by 1000s, then you wouldn't need to ripple any at all

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •