re-ordering and sorting 4

[nicklieb]

Hi guys,

need a pointer here, on a problem I'm trying to work through.

I have a list of variables that I want to have a priority assigned to, each line has drop down that corresponds to their priority ( ie 1 to ~).

I want to be able to move a variable to any position in the list and then adjust the items according to where that variable was placed.

A   1
B   2
C   3
D   4
E   5    ..etc

now say I want to move E to position 1 as follows

E    1
A    2
B    3
C    4
D    5

Once this sort has been done it then need to update all the database once the sort is complete.

My first thought was a bubble sort, fairly simple I though.

I have a table within Sql that has the ranking positions in them, now I need to update these sets of data. so was logic was as follows:

1. pull data from database and insert into array
2. create a new array looping through the array (created in 1) until I hit my required position.
3. Add the changed reference into the array and then carry out transferring data from the first arrya, ensuring that the reference was not duplicated
4. loop through the built array and re insert into the database.


Abit long winded no?? And the list could be very long upto 1000 lines potentially ( thereby making the script a tad slow)

There must be an easier way to do this? Has anyone had any experience and give me a pointer of a better way to approach this problem.

Many thanks in advance

 

[Sheco]

Is the ultimate goal to update the database table with the new rankings?

 

[Sheco]

Let me see if I understand:

1. You have a database table where each record has a sequential ranking.

2. You want to update the ranking of a record to move it up or down in the sequence.

3. No two records can have the same ranking so the rankings of the other records must be adjusted up or down to accomodate the updated ranking from step #2

 

[Sheco]

You can do this strictly using the database if you can add another field.

Lets say your table has 3 fields populated as follows:

_Letter_    _Rank_   _NewRank_
   A          1        Null
   B          2        Null
   C          3        Null
   D          4        Null
   E          5        Null

To move E up to rank 2 you might do this:
First get these values however makes sense for your app:
MyLetter = "E"
MyNewRank = 2
MyCurrentRank = 4


Then execute a series of SQL commands like this:
UPDATE MyTable
SET NewRank = MyNewRank
WHERE Letter = MyLetter

UPDATE MyTable
SET NewRank = Rank
WHERE (Rank > MyNewRank) OR (Rank < MyCurrentRank )

UPDATE MyTable
SET NewRank = (Rank + 1)
WHERE (Rank <= MyNewRank) AND (Rank > MyCurrentRank )

UPDATE MyTable
SET Rank = NewRank

UPDATE MyTable
SET NewRank = Null

 

[steven290]

here is a sub i created a while ago to do what you want

sub changesortorder(cn,tablename,idfield,sortfield,idtoupdate,origpos,newpos)
	if cint(newpos) > cint(origpos) then
		ssql="UPDATE "&tablename&" set "&sortfield&"="&sortfield&" - 1 where "&sortfield&" > "&origpos&" and "&sortfield&" < "&newpos&";"
		cn.execute(ssql)
		ssql="UPDATE "&tablename&" set "&sortfield&"="&newpos&"-1 where "&idfield&"="&idtoupdate&";"
		cn.execute(ssql)
		response.Write(ssql)
	else
		ssql="UPDATE "&tablename&" set "&sortfield&"="&sortfield&" + 1 where "&sortfield&" >="&newpos&" and "&sortfield& "< "&origpos&";"
		cn.execute(ssql)
		ssql="UPDATE "&tablename&" set "&sortfield&"="&newpos&" where "&idfield&"="&idtoupdate&";"
		cn.execute(ssql)
	end if
	cn.Close
	Set cn = Nothing
end sub

 

[Sheco]

Well I wrote move E up to 2 ... for some reason my brain didn't contact my fingers ... or I live in an alternate reality where there is no D ... where E is the 4th letter of the alphabet.

Other than that the example stands.

 

[Sheco]

Ah Steven's approach is more elegant because he doesnt use a separate NewRank field but insteads operates directly on the Rank.

 

[vongrunt]

Trivia question: how to make the same, but without if's (checking for direction)?

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[Sheco]

Looks like the first branch of the IF should use >= instead of just >

    if cint(newpos) > cint(origpos) then
        ssql="UPDATE "&tablename&" set "&sortfield&"="&sortfield&" - 1 where "&sortfield&" >[red][b]=[/b][/red] "&origpos&" and "&sortfield&" < "&newpos&";"
 
....

Otherwise you end up with 2 rows at the same rank.

 

[steven290]

usage:

changesortorder conn,"tblname","idfield","sortfield",idtoupdatevar,origposvar,newposvar

 

[steven290]

actually my method is meant for when you want to do a

"Place before:"
then you chhose from dropdown

with the last value set to +2 of ubound(arr,2),and it says "make last"

i use the getrows method

 

[nicklieb]

thanks for input guys. I;m trying stevens sub, and modding it to my specific requirements.

My requirements are abit complex than I indicated in my first post.

I am writing test scripts at the moment

Changing order for GLRs for 101116 setting to rank no from 48 to 1
100941:1
100618:2
101418:3
100737:4
100890:5
100855:6
101025:7
101447:8
101006:9
100438:9
100667:10
100684:11
100888:12
100769:13
100940:14
100821:15
100886:16
100919:17
100832:18
101445:19
100596:20
100766:21
100846:22
101220:23
100677:24
100932:25
100983:26
101460:27
100798:28
101466:28
100815:29
100845:30
101423:31
100438:32
101489:33
100317:34
100793:35
101422:36
100858:37
101419:38
100859:39
100995:40
100554:41
100860:42
101421:43
101200:44
101133:45
101133:46
101184:47
101116:48 changes to 1

now this is a list with current ranking, the first number a referencing a request.

In the ranking table I will hold all of the different divisions in the case of the one above is refers to GLR, now there are 5 other divisions that are held in the ranking table, each with references to another table.

i am still grappling with the sub and will let you guys knwo the outcome

 

[Sheco]

I noticed a pair of dupes.

101006:9
100438:9

100798:28
101466:28

 

[nicklieb]

hmm yeah I see that, I've been messing with ranking all afternoon, and am starting to get some where. those dupes are prob from one of the tests, but I reset them all when the is done

 

[Sheco]

OK but if they show back up check my post about about a > that should have been a >=

 

[steven290]

definitely look into sheco's, like i said my version is meant as a "Place before a certain record feature" - I believe sheco's is to place in the exact position

mine

take 1,9 make 1 into 8, 9 stays

shecos

1,9 make 1 into 9, 9 into 8

 

[Sheco]

but yours will do the same as mine if you just add the equal sign

 

[Sheco]

plus you didn't use an extra column, i like that.

 

[steven290]

yes, for that i thank you

 

[steven290]

actually you don't need it because with the second update updates it to new position