Zugg Software :: View topic

Adept Joined: 30 Dec 2003 Posts: 226

Is it possible to sort a DB variable by value? Highest value to lowest value, or vice versa?

Adept Joined: 30 Dec 2003 Posts: 226

So I take it nobody has a way to do this?

SubAdmin Joined: 18 Nov 2001 Posts: 5182

Do you want to sort on the keys or the values?

Adept Joined: 30 Dec 2003 Posts: 226

I want to sort based on the values, Highest to lowest

Apprentice Joined: 26 Oct 2004 Posts: 108

#VIEW

according to the helpfile this will alter the view of a currently open database.
This meaning that if you have a database named "Weapons" and use the #view command as this:

#VIEW NewView {Name} {Name|Hit|Dam|Damage}

Create a new view called NewView that is sorted by Name and displays the fields Name, Hit, Dam, and Damage

This was a direct copy from the helpfile. Hope you'll have some use out of it.

Adept Joined: 30 Dec 2003 Posts: 226

Hrm... I'm not using an actual database though... It's a DB variable :P

MASTER Joined: 28 Nov 2000 Posts: 4817 Location: USA

You'll probably have to write your own sort routine. There is a %sort function, but it applies to list variables rather than database variables and does an alphanumeric sort which doesn't sound like what you want. In an alphanumeric sort, the items are sorted by the first non-matching character, which means that 21 comes between 1 and 3.

Adept Joined: 30 Dec 2003 Posts: 226

Hmm..... Any ideas on where to start with something like that, especially since sort wouldn't really work, even if it was a list var? Confused

MASTER Joined: 28 Nov 2000 Posts: 4817 Location: USA

Try looking through beginning Computer Science textbooks or something similar for a routine you can adapt to zScript. Sorting is required for so many things that it would almost certainly be covered.

Beginner Joined: 16 Mar 2004 Posts: 20

While I'm here I guess I will answer a question in payment for my question being answered.
#LOOPDB @dbvar {#Var SortedList %sort(%val|@Sortedlist)};
%sort also has a param for reverse sorting.

Solem of AardWolf
Builder of fine Finite State Machines.

Adept Joined: 30 Dec 2003 Posts: 226

Doesn't seem to work, Solem.... I'm wanting to sort the DB itself, not make a list of the values in sorted order.

SubAdmin Joined: 18 Nov 2001 Posts: 5182

First step is to reverse the key/value association and do any needed massaging of the data.

Assuming we are working with numbers for values:
#VAR temp {}
#LOOPDB @recvar {#ADDITEM temp {%concat(%right(%concat("0000000000",%val),10),"=",%key)}}

We can then sort:
temp=%sort(@temp)

And to make a new record variable
#VAR temp2 {}
#ADDKEY temp2 {@temp}

Finally we can replace the original with the new one, once again reversing the fields and stripping the padding zeroes.
#VAR recvar {}
#LOOPDB temp2 {#ADDKEY recvar {%val} {%eval(%key)}}
#UNVAR temp
#UNVAR temp2

Adept Joined: 30 Dec 2003 Posts: 226

Thanks, Vijilante, but I have encountered a couple of problems. First, the %right is getting rid of the padding zeros for some reason, it's not working how it seems it should from the help file... When I have %right(%concat("0000000000",%val),10) it gets rid of all the zeros. if I do %right(%concat("0000000000",%val),9) it adds on one zero, %right(%concat("0000000000",%val),8) has 2 zeros, etc. This seems really strange to me, as I thought it was supposed to be counting out 10 from the right and then stopping, but instead it is getting rid of the first 10 characters.

Secondly, there's a problem with putting the values into temp2, since many times I will have the same number of multiple things in the dbvar. IE, I have 2 shoes and 2 pants (Just example) Then, after sorting, when it puts it back in the temp2 variable, whichever of those two is last on the list will overwrite the other, since you can't have multiple keys that are the same. So, if I have 10 items in the dbvar and 3 of them have the same value, I only end up with 8 in the resulting variable.

I'm gonna work on these a bit, but if you have ideas on how to make it work, do tell Laughing

Apprentice Joined: 26 Oct 2004 Posts: 108

Actually, It's a tricky formulation, but the memory aid line in the helpfile gives a minor hint about it..

that %left(anystring, @anynumber) plus %right(anystring, @anynumber) equals anystring.

Meaning that it doesn't take the right-most section and backwards 10, but from position n it takes either to the left or the right-most part of it.

string = "123-4-567"
%right(@string, 5) -> 4-567
%left(@string, 5) -> 123-

Meaning both %right and %left originate from the same point. Not from either the left-most end of the string or the right. Meaning for %left it's "from the beginning until..." and for %right it's "from this point on...".

You'd want to use the %rightback function...

Example:

#show %rightback(%concat("0000000000",23),10)
gives: 0000000023
#show %rightback(%concat("0000000000",12223),10)
gives: 0000012223

And as for the uniqueness problem... MAKE them unique... add a "count" variable to them... if you have multiples of shoes with same name and value. make your own count if needed... but don't they have this 10-digit number to make them unique? If not, make one. give them an ID number. Or just a count... your choice.

SubAdmin Joined: 18 Nov 2001 Posts: 5182

Yep, should be %rightback, that is what happens when I don't actually test stuff. To solve the problem of duplicate deletion I would first change the #ADDITEM to %additem since that allows duplicates, then I would manually create the record variable. This will ensure that all data stays intact no matter how you have duplicated things.

#VAR temp {}
#LOOPDB @recvar {temp=%additem(%concat(%rightback(%concat("0000000000",%val),10),"=",%key),@temp)}
temp=%sort(@temp)
#VAR recvar {}
#FORALL @temp {#IF (@recvar) {recvar=%concat(@recvar,%char(29))};%recvar=%concat(%word("%i",2,"="),%char(30),%eval(%word("%i",1,"="))}
#UNVAR temp

Adept Joined: 30 Dec 2003 Posts: 226

Well, I've got a defininte start, but I have a couple of problems still. I havn't been able to get the padding of zeros in to make it sort higher numbers right, and I've run across a problem when my original key has a comma in it. Here's my testing alias so far:

#AL testsorting {#VAR temp {}
#LOOPDB @testing {#ADDITEM temp %concat( %val, "=", %key)}
temp = %sort( @temp, 1)
#VAR temp2 {}
#FORALL @temp {
%match( "%i", "(%d)=(*)", tempval, tempkey)
#GAG
#ADDK temp2 {@tempkey} {@tempval}
}
}

@testing is my dbvar I'm using to test this. If I could get the zeros padding in, this would work perfectly except if the original key has a comma in it, IE "a long, thin sword" or something like that. For some reason, this is will not be put into @temp2, it's just left off... it works with the %match and is put into the variable correctly, as far as I can tell, but it is not put into @temp2.

EDIT - Doh, i just realized that I needed quotes around the %i, added them in now. Now I just need the zero padding...

Adept Joined: 30 Dec 2003 Posts: 226

Lol, wow... in the 10 min or so I worked on this and then posted, you had already posted replies... thanks guys Laughing

Adept Joined: 30 Dec 2003 Posts: 226

Hmm... vigilante's script didn't work for me, but I was able to use the %rightback to change mine so it did. Is there any way to make it so %match doesn't send display/send a 1 or 0? The gag seems to be not working.

#ALIAS sortdb {
#VAR temp {}
#LOOPDB @%1 {#ADDITEM temp %concat( %rightback( %concat( "0000000000", %val), 10), "=", %key)}
temp = %sort( @temp, 1)
#VAR %1 {}
#FORALL @temp {
%match( "%i", "(%d)=(*)", tempval, tempkey)
#GAG
#ADDK %1 @tempkey {%eval( @tempval)}
}
#UNVAR temp
#UNVAR tempval
#UNVAR tempkey
}

Adept Joined: 30 Dec 2003 Posts: 226

I keep fixing things. I got around the sending by making it #ECHO %match(....

So, if anyone wants my finished script:

#ALIAS sortdb {
#IF (%1) {
#VAR temp {}
#LOOPDB @%1 {#ADDITEM temp %concat( %rightback( %concat( "0000000000", %val), 10), "=", %key)}
temp = %sort( @temp, 1)
#VAR %1 {}
#FORALL @temp {
#ECHO %match( "%i", "(%d)=(*)", tempval, tempkey)
#GAG
#ADDK %1 @tempkey {%eval( @tempval)}
}
#UNVAR temp
#UNVAR tempval
#UNVAR tempkey
}
}

Wizard Joined: 15 Jan 2002 Posts: 1644 Location: USA

#COMMANDS execute something
%functions return something

#CALL discards the return from a function

#CALL %match(.......)
will execute execute the %match and discard the uneeded 1/0 return which is what you want in this case also it gets rid of the need for the #GAG

Adept Joined: 30 Dec 2003 Posts: 226

Thanks, that's exactly what I needed for this Very Happy