Sorting data - Array or Dictionary with 5 (columns)

I need to implement a sorting capability on a partial area of a listbox. There are 5 columns per row of information that when sorted need to stay together.
There are two specific types that are always defined in column-1. In each row columns 0-5 need to be copied. There are more columns that 5 but only need to deal with these columns in each row. I have implemented a way to identify each sets beginning row and have this method able to address variable listbox rows.

The thought is to copy the rows of a specific type into an array or dictionary. I have used multidimensional arrays before but from what I have read a dictionary may be the better direction. If so how to use a dictionary with multiple (columns?) alludes me and then how to sort and maintain data integrity. Or is this not a way to use a dictionary and if not what would be a good way to accomplish.

I see how to do this with a db as well but perhaps this is time to learn dictionaries beyond the simple example XOJO provides.

FYI: After doing the sorting I would use the Dictionary info to write back to the listbox then perform a db update of that newly sorted data in that Listbox.

Any thoughts on which direction and how to begin?

first off… a dictionary isn’t really “sortable”… its a key indexed collection… optimized to return a value that matches a provided key.
An array is sortable (you could create a composite sort key and use the build in SORT function).

Why not copy the “rows” into an in-memory database table, and select them back with an ORDER BY statement?

A lot will depend on the number of rows involved? 5? 10? 1000? 10000? (any more and I’d rethink your entire process)

Leave the data in the listbox but implement the CompareRows method so you can compare two row’s values using whatever logic you want.

Then either allow the column headers to be clicked by the user, and / or initiate the sort by a specific column yourself from code using the Sort method for the listbox object.

When you implement the CompareRows() method, you have complete control on how it sorts. You can interpret data how you want, and when the values in two rows are equal in the requested sort column, then do secondary compares on additional column(s) as needed to get the exact order you want.

When you load the listbox, if coming from a database use the ORDER BY clause to get the initial sequence you want. If you are loading the rows unordered, then you can populate the listbox rows but call Sort yourself while presenting the initial data.

@Dave S - In the end thats exactly what I did. Row needs are less than 200 with 8 columns.

[quote]1st create an in memory sqlite db and table.
Fill the table with the portion of the listbox that is required for sorting.
Then I did a recordset request of that sqlite table sorted by the field of need.
Next I wrote this back out to that portion of the listbox.
Lastly completed with a db update that mirrored the entire listbox.[/quote]

I did this on a WE app using Graffiti Suite Grid as the listbox. Works great. In cell updates and changes just like on a desktop.

Thanks for everyones suggestions

I ran into a little hitch using SORT on SQLLITE. That being I have the following I want to SORT in a single column

[quote]SPEED 7
SPEED 12[/quote]

When I run SORT this is the order

[quote]SPEED 12
SPEED 7[/quote]

Which i snot what I want. I attempted several syntax aspects similar to MySQL to address but no solution.

Is there a syntax that would enable sort so the order is

[quote]SPEED 7
SPEED 12[/quote]

I have used ORDER BY CAST(Name_Label As TEXT) or ORDER BY CAST(Name_Label As INT) but neither gives results needed.

Using ORDER BY CAST(Name_Label As TEXT) is the same as ORDER BY Name_Label

I don’t have the syntax right off, but you would have to split that value into a string and a NUMBER (not another string) and use those in the ORDER BY…
The way you have it, the ORDER BY results are in fact correct.

The problem here is that both are being interpreted as a single string, so “SPEED 1…” comes before SPEED 7". So the ORDER BY is working as designed.

Note that this is one of the things you can tweak if you use the listbox CompareRows() method I mentioned above. You can code you own logic to “compare” two values even if that means doing whatever custom logic you want. Such as splitting entries by words, then for each word, dynamically compare them as string or numeric values depending on content.

To get anything similar in an ORDER BY clause, you’d have to provide a UDF (User Defined Function) to override the value of the ORDER BY, perhaps by imputing leading zeros to normalize numeric values. But I’m not even sure the default Xojo SQLite client will let you provide a custom UDF. (Never had a need to try, so have not gone looking.)

you can munge the data when you put it in the sqlite database in any way you want
keep all your raw data in one set of columns
when you encounter SPEED 7 or others like it split it into 2 parts “SPEED” and the integer “7” or whatever
and insert those into other columns you use just for the order by portion of the select statement

It does not… I just tried

Maybe it is an idea to change SPEED 7 into SPEED 07.
(Make all numbers 2 or more digits)
Then it will sort correctly.

here is something that seems to work

select s

from (select *,
case instr(s," ") 
when 0 then length(s)
instr(s," ") 
end as pos from abc)

order by upper(substr(s,1,pos)),cast(substr(s,pos+1) as Int)

Ihe upper can be left out if your table has NOCASE

@Dave S I attempted to convert your example into what I think I need without success. So here are the steps I use to get to the SORT needed

First of all thanks for all the responses. This is an important aspect of this community I do not find in other tech communities.

  1. Dim my_SPD_table As New SQLiteDatabase
  2. If my_SPD_table.Connect Then Dim sql As String sql = "CREATE TABLE SPD (ID INTEGER NOT NULL," + _ "BTN TEXT," + _ "Type TEXT," + _ "Name_Label TEXT," + _ "Number TEXT," + _ "BLF_Destination TEXT," + _ "BLF_Idle TEXT," + _ "INT_Source TEXT," + _ "INT_Dest TEXT," + _ "PRIMARY KEY(ID));" End

  3. sql = "INSERT INTO SPD (BTN,Type,Name_Label,Number,BLF_Destination,BLF_Idle,INT_Source,INT_Dest) VALUES " + _ "('" + Phone_Info_LB.Row(x).Cell(Phone_Info_LB.Column(0)) + "'," + _ "'" + Phone_Info_LB.Row(x).Cell(Phone_Info_LB.Column(1)) + "'," + _ "'" + Phone_Info_LB.Row(x).Cell(Phone_Info_LB.Column(2)) + "'," + _ "'" + Phone_Info_LB.Row(x).Cell(Phone_Info_LB.Column(3)) + "'," + _ "'" + Phone_Info_LB.Row(x).Cell(Phone_Info_LB.Column(4)) + "'," + _ "'" + Phone_Info_LB.Row(x).Cell(Phone_Info_LB.Column(5)) + "'," + _ "'" + Phone_Info_LB.Row(x).Cell(Phone_Info_LB.Column(6)) + "'," + _ "'" + Phone_Info_LB.Row(x).Cell(Phone_Info_LB.Column(7)) + "');"
    The above grabs the section of the Graffiti Grid (WEB LISTBOX) and INSERTS into each row using a FOR NEXT statement to track starting and ending position. When sorted I need the row of information to remain in tack after the sort. There are 14 columns to this table and the last 4 columns are DON’T CARES

Then to continue perform
4. Dim _rs As Recordset

  1. sql = "SELECT * FROM SPD WHERE Type = 'SPD' ORDER BY CAST(Name_Label As TEXT)"
    Is what I used to do to grab all columns and sort by Name_Label

So I have modified several times your example and am currently on the following. All modifications have lead to errors when implementing this last step as _rs always = NIL
6. _rs = my_SPD_table.SQLSelect(sql)

Here is the last modified sql statement

sql = "SELECT * FROM SPD(SELECT Name_Label," + _ " CASE INSTR(Name_Label,"" "")" + _ " WHEN 0 then length(T Name_Label ype)" + _ " else" + _ " instr(Name_Label,"" "") " + _ " end as pos from abc)" + _ " WHERE Type = 'SPD' " + _ " order by upper(substr(Name_Label,1,pos)),cast(substr(Name_Label,pos+1) as Int)"

which errors

Replace “ABC” with the correct table name

SELECT name_label
CASE INSTR(name_label," ") 
WHEN 0 THEN LENGTH(name_label)
INSTR(name_label," ") 
ORDER BY UPPER(SUBSTR(name_label,1,POS)),CAST(SUBSTR(name_label,POS+1) AS INT)

Thanks - still not working

Tried this

sql = "SELECT * (SELECT Name_Label," + _ " CASE INSTR(Name_Label,"" "")" + _ " WHEN 0 then length(Name_Label)" + _ " else" + _ " instr(Name_Label,"" "")" + _ " END AS pos FROM SPD)" + _ " order by upper(substr(Name_Label,1,pos)),cast(substr(Name_Label,pos+1) as Int)"

And this

sql = "SELECT * FROM SPD(SELECT Name_Label," + _ " CASE INSTR(Name_Label,"" "")" + _ " WHEN 0 then length(Name_Label)" + _ " else" + _ " instr(Type,"" "") " + _ " END As pos FROM SPD)" + _ " ORDER BY UPPER(substr(Name_Label,1,pos)),cast(substr(Name_Label,pos+1) as Int)"

And this

sql = "SELECT * FROM SPD (SELECT Name_Label," + _ " CASE INSTR(Name_Label,"" "")" + _ " WHEN 0 then length(Name_Label)" + _ " else" + _ " instr(Type,"" "") " + _ " END As pos)" + _ " ORDER BY UPPER(substr(Name_Label,1,pos)),cast(substr(Name_Label,pos+1) as Int)"

all 3 leave _rs = NIL

Just tried this

sql = "SELECT * (SELECT Name_Label," + _ " CASE INSTR(Name_Label,"" "")" + _ " WHEN Name_Label = 0 THEN length(Name_Label)" + _ " ELSE" + _ " INSTR(Name_Label,"" "")" + _ " END AS pos FROM SPD)" + _ " ORDER BY UPPER(substr(Name_Label,1,pos)),cast(substr(Name_Label,pos+1) as Int)"

Well, MBS does let you name an alternate SQLite engine, but in this case that seems like going around the world to cross the street.

If one can control the database, then adding additional columns to use in the ORDER BY as Norman mentions leaves the visible data unchanged. Or left padding numbers with zeros as Paul mentions works too.

But it still seems to me the CompareRows() method is the super easy way to fix this – that is why the override method exists! And since this listbox evidently has different types of data, but Carl already has a function to determine the proper style for a row, just call that from within CompareRows() as the primary sort criteria regardless of column, then sort as needed within type.

The code I provided is what should work. If you are getting RS=NIL this means there is a syntax error… What is the DB Error message

I tested it extensively with a same database (the original ABC) and it worked well, and did not require any modifications to the database or the data, and works with both items with a number and those without

Sorry, Douglas, but I have to disagree completely here. Let the data source do the work, because perhaps a time will come when there is no listbox involved?

And while Normans way “works” its a subset of my query… but adds overhead to the table, where it might be that the developer runs across a sitiuation with they don’t have the luxury of altering the structures

@Douglas Handy - Thanks - This is a Graffiti Grid (WE LISTBOX) and there is no Compare Row Event Handler. Plus the sort needs to occur on a section of the WE LISTBOX not the entire WE LISTBOX. This sql statement is one to do the work. I have performed others like this but not to this extent. At this moment it is figuring out the syntax to yield results.

I just created a table named SPD, with a field called name_label and the code I posted above works perfectly.

SELECT name_label
          CASE INSTR(name_label," ")
          WHEN 0 THEN LENGTH(name_label)
          ELSE INSTR(name_label," ") END AS POS
          FROM SPD)
 ORDER BY UPPER(SUBSTR(name_label,1,POS) ) , CAST ( SUBSTR(name_label, POS + 1) AS INT)

@Dave S - my bad - I totally missed this piece from you

[quote]SELECT name_label
CASE INSTR(name_label," “)
WHEN 0 THEN LENGTH(name_label)
INSTR(name_label,” ")
ORDER BY UPPER(SUBSTR(name_label,1,POS)),CAST(SUBSTR(name_label,POS+1) AS INT)[/quote]

I made the following minor change for XOJO syntax for "

sql = "SELECT * " + _ "FROM (SELECT *, " + _ "CASE INSTR(name_label,"" "") " + _ "WHEN 0 THEN LENGTH(name_label) " + _ "ELSE " + _ "INSTR(name_label,"" "") " + _ "END AS POS FROM SPD) " + _ "ORDER BY UPPER(SUBSTR(name_label,1,POS)),CAST(SUBSTR(name_label,POS+1) AS INT)"

Then when I perform this

_rs = my_SPD_table.SQLSelect(sql)

All is good. Freaking awesome. And as you point out this can be used when no listbox is available as in this case it is a Graffiti Grid (WE LISTBOX) I am using which is really a powerful WE ListBox alternative

FYI: The error was “ERROR NEAR ‘(’”

Thanks to all for your thoughts and suggestions

Apologies Dave for missing your earlier post.

Forum for Xojo Programming Language and IDE. Copyright © 2021 Xojo, Inc.