Sorting a Ranking Field

[grahowler]

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.

[r937]

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

[grahowler]

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.

-- 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

-- 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

[r937]

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

[grahowler]

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.

[r937]

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