Sorting a two dimensional array using the sort command.

[texaspete]

I am wanting to use lb sort command to sort a two dimensional array on two fields at once.
I would like to sort the array based on the "city" and then I would like to sort the array based  on "zip" .
Would I simply sort it twice Like so, or is there and easier way?

 Sort arrayName$() 1,100, 5 "Note 5 is for the field for the city
Sort arrayName$()  1,100, 10 "Note 10 is for the field of the zip

I am just trying to make sure I have this right in my head. 
The array width  is actually 32 wide and a depth of 1000 records

Thanks, Texas Pete

[tenochtitlanuk]

Done by two sorts, the second would undo the first.
You need to do the first sort, using the whole 2D table, then locate the start and end of the required bit and re-sort using the new column but specifying the start and finish.. I reckon.

[tenochtitlanuk]

This illustration code works for me- the datafile is at www.diga.me.uk/UNdata_Export_20190104_222603438.csv if you want to try it.
NB I work on Linux, so you may need to change LF<>CRLF.
Note that the second time round I sort only data between the beginning and end rows that I found. Since it is a text field, the sort puts say 1 and 100 before 2. Beware!
If you want, after the first sort, to sort EACH block with a common value, you'd need to repeat the third stage appropriately. I don't know what your data represents, so can't check if you want this- need to see your data tanble really...

   dim data$( 10000, 10)

   open "UNdata_Export_20190104_222603438.csv" for input as #fIn   '241 rows in 6 columns
       print "Unsorted original data, showing only columns 2, 3 and 4"
       for i =1 to 241
           line input #fIn, line$
           '   "Country or Area","Sex","Age","Type of living quarters","Value","Value Footnotes"
               for j =1 to 6
                   data$( i, j) =word$( line$, j, ",")
               next j
           'print data$( i, 1); ","; data$( i, 2); ","; data$( i, 3); ","; data$( i, 4); ","; data$( i, 5); ","; data$( i, 6)
           print data$( i, 2); ","; data$( i, 3); ","; data$( i, 4)
       next i

   close #fIn

   sort data$(), 2, 241, 4  '   sort by column 4 #type of living quarters'

   print ""
   print "Sorted by column 4"

   for i =1 to 241
       'print data$( i, 1); ","; data$( i, 2); ","; data$( i, 3); ","; data$( i, 4); ","; data$( i, 5); ","; data$( i, 6)
       print data$( i, 2); ","; data$( i, 3); ","; data$( i, 4)
   next i
   print ""
   print "Finding first and last rows with 'roofless' in column 4"
   '   Find first appearance of 'Roofless' and last in column 4
   firstRow =0
   lastRow  =0

   for i =1 to 241
       if data$( i, 4) =chr$( 34) +"Roofless" +chr$( 34) and firstRow =0               then firstRow =i
       if firstRow <>0             and data$( i, 4) <>chr$( 34) +"Roofless" +chr$( 34) then lastRow  =i -1: exit for
       '   since we detected we'd just past it..
   next i

   print "First was row "; firstRow; " and last was "; lastRow

   print ""
   print "Now sorting by column 3 ( 'age') BUT only the rows in-range' leaving rest as-is"
   '   now sorted by column 3 'age' and column 4 'type of accomodation'
   sort data$(), firstRow, lastRow, 3

   for i =1 to 241
       'print data$( i, 1); ","; data$( i, 2); ","; data$( i, 3); ","; data$( i, 4); ","; data$( i, 5); ","; data$( i, 6)
       print data$( i, 2); ","; data$( i, 3); ","; data$( i, 4)
   next i

   end



[mknarr]

Here is the method I use to sort on two columns:

dim array$(20,3)
       array$(1,1)="Hunter":array$(1,2)="Peter":array$(1,3)="1"
       array$(2,1)="Brown" :array$(2,2)="James":array$(2,3)="2"
       array$(3,1)="Andrew":array$(3,2)="Billy":array$(3,3)="3"
       array$(4,1)="Hunter":array$(4,2)="Peter":array$(4,3)="4"
       array$(5,1)="Andrew":array$(5,2)="Peter":array$(5,3)="5"
       array$(6,1)="Brown" :array$(6,2)="Barry":array$(6,3)="6"
       array$(7,1)="Hunter":array$(7,2)="John":array$(7,3)="7"
       array$(8,1)="Carl":array$(8,2)="Donald":array$(8,3)="12"
       numberrecords=8
       for x=1 to numberrecords:print array$(x,1);", ";array$(x,2);", "; array$(x,3):next:print
       sort array$(), 1, numberrecords, 1 'Sort by last name first.
       for x=1 to numberrecords:print array$(x,1);", ";array$(x,2);", "; array$(x,3):next:print
       count=0
       first=0
       for x=1 to numberrecords 'Next sort the array by the first name.
           if array$(x,1)=array$(x+1,1) then 'If two last names match
               if first=0 then 'and it's the first time a match is found
                   first=x 'then the 'first' counter will be set to the current x position
                   count=first+1 'and the 'count' counter will be set to the next matching element.
               else            'If the second or more data point match
                   count=count+1 'then just increase 'count' counter by 1.
               end if
           else 'If two last names do not match
               if count>0 then 'and the 'count' counter is greater than zero
                   sort array$(), first, count, 2 'then sort the first name column
                   first=0:count=0 'Reset counters.
               end if
           end if
       next
       for x=1 to numberrecords:print array$(x,1);", ";array$(x,2);", ";array$(x,3):next:print
       end

[alincon]

I've posted this message many times:
Make a preliminary pass through the file to be sorted and concatenate the two (or more) columns to be sorted into column zero
Then sort on column zero.

r.m.

[tenochtitlanuk]

Don't see how that would work, alincon.

Diagram below shows one and two columns being sorted..

[Rod]

Not on my PC but creating an indexing column is pretty standard. You can create as many indexing columns as your database query needs.

So you might have first name second name and say zip code as separate columns. If you want to find on a regular basis second names in a particular zip code you would create a new column and fill it with zip code+second name.

Now when you want to find names in a zip code you simply sort on the new column. Then find the zip code and names you want.

These indexing columns need not be created on the fly you can ”maintain” them as part of the data set when new info is added.

So the associated data in a multi dimensioned array is always associated, you are not changing that association just the order that the entire line is displayed.

[tenochtitlanuk]

I had understood 'concatenate the columns' to mean end-to-end.
In fact what was meant was I gather 'concatenate the terms in the two columns, row by row, to make a new column'.

Many ways to achieve end result, as always.

Personally I use spreadsheets and databases to do this kind of data handling, although grateful on occasion for LB's 'sort'.

[BeeTrap]

Hello, just my two cents worth........
I have used alincon's code from a post dated 2001 in the old ConForums
Liberty BASIC site and it has served me well. I have NOT compared what
I have to what is listed here, but the code I do use is the only way

that seemed to work for my Grocery List prog.

Oops: that code was from "Alincon2001" not alincon in 2001, sorry!

Code still good, just not my memory..........

Code was posted Dec 14, 2005.

' CODE -- from Alincon2001 article "Sort on 2 Fields", just what I needed
'   to be able to "sort" my grocery list items by "category" and/then
'     "item name".  Any other "sort" would NOT work for me.
'       cwr mod 02-19-2017
'

while eof(#1) = 0
 incCtr = incCtr + 1
 line input#1, incRec$
 incRec$=mid$(incRec$,2)
 for m = 1 to 6
   incRec$(incCtr,m) = word$(incRec$,m,"/")
 next m
wend
' end code

' Then you can use a subroutine like this to sort on more than one field
' CODE --

call twoFieldSort expCtr,5,1
.....
sub twoFieldSort c1,f1,f2
 for n = 1 to c1
   expRec$(n,0) = expRec$(n,f1) + expRec$(n,f2)
   'print expRec$(n,0); " "; str$(n)
 next n
 sort expRec$()1,c1,0
end sub
.....
' end code


[texaspete]

Thank you for every ones input. I will look and read over everything and then let you know how i come out.
Thanks TexasPete