# Listbox alpha numerical sort?

There a many conversation about listbox sort, but I can’t find one for alpha numerical sort. I would like to sort a column list like SQL sort or sort a column in MS Excel.

Table entries without sort
1
All good things
Xojo
10
5
102
Zulu
23
45
9
1009

Table entries with sort in SQL: select * from tableSort order BY name*1, name

All good things
Xojo
Zulu
1
5
9
10
23
45
102
1009

How can I do this with CompareRow?

off the top of my head, based on the COMPAREROWS info in the Lang.Ref…

``````Function CompareRows(row1 As Integer, row2 As Integer, column As Integer, ByRef result As Integer) As Boolean
dim data1 as string=me.cell(row1,column)
dim data2 as string=me.cell(row2,column)

if not isNumeric(data1) or not isNumeric(data2) then
// at least one value is NOT a number
Return False
else
If Val(data1) < Val(data2) Then
result = -1
ElseIf Val(data1) > Val(data2) Then
result = 1
Else
result = 0
End If
Return True
end if
End Function``````

This code has NOT been tested, and is offered for illustration purposes only… As such it should not be considered a cut/paste solution, and should be evaluated by each developer to determine if or how it might fit their situation

Yes, I know, but this works only if the contents of a cell is only numeric or text. I read some files from a folder like this:
1.png
All good things.png
Xojo.png
10.png
5 All or nothing
102.png
Zulu.png
23.png
45.png
9.png
1009.png

SQL can sort this like this:
All good things.png
Xojo.png
Zulu.png
1.png
5 All or nothing
9.png
10.png
23.png
45.png
102.png
1009.png

Use CompareRows as indicated above, but youll have to check the contents. You could use Val on those strings, and if two values return the same value, then use a string compare. That would get you closer, but not handle things like 100-100.jpg

```dim s1 as String = me.Cell(row1,column) Dim s2 as String = me.cell(row2,column) Dim v1 as double = Cdbl(s1) Dim v2 as Double = Cdbl(s2) If v2<>v1 then Result = Sign(v2-v1) Return True Else if s1 <> s2 then Result = StrComp(s1,s2) Return True End if Return False```
which now that I look at it again is nearly identical to what Dave did in less lines…

Thanks for your answer. I have create a in-memory sqlite database. Put the file names in database, sort in database and push the sorted file names into ListBox.

@Horst Jehle - you could also use a string array, use sortwith and copy to listbox.

[quote=381636:@Horst Jehle]
SQL can sort this like this:
All good things.png
Xojo.png
Zulu.png
1.png
5 All or nothing
9.png
10.png
23.png
45.png
102.png
1009.png[/quote]

Ummmm… no, no it doesn’t… SQL will sort “strings” the same way Listbox does normally

``````create table test (xyz text);
insert into test values('All good things.png');
insert into test values('Xojo.png');
insert into test values('Zulu.png');
insert into test values('1.png');
insert into test values('5 All or nothing');
insert into test values('9.png');
insert into test values('10.png');
insert into test values('23.png');
insert into test values('45.png');
insert into test values('102.png');
insert into test values('1009.png');
SELECT xyz FROM test ORDER by XYZ;``````

Results

``````"1.png"
"10.png"
"1009.png"
"102.png"
"23.png"
"45.png"
"5 All or nothing"
"9.png"
"All good things.png"
"Xojo.png"
"Zulu.png"``````

Use this:
SELECT xyz FROM test ORDER by XYZ*1, XYZ;

[quote=381695:@Horst Jehle]Use this:
SELECT xyz FROM test ORDER by XYZ*1, XYZ;[/quote]
interesting… and if you think about it, that is almost exactly what the CompareRow suggestion does

FYI… while it works with SQLite, I’d be interested in what a database with strict datatype constraints would do

I tested without the `, XYZ` after *1 and it works too.

I found some MySQL reference about using +0 ordering varchar columns, so I tried this:

``SELECT xyz FROM test ORDER by XYZ+0;``

and it also works. Maybe using *1 or +0 is the same? Is it better to use it like this?:

``SELECT xyz FROM test ORDER by XYZ+0, XYZ;``

In what situation the extra `, XYZ` is needed?

you need both xyz*1,xyz to work properly

if the value is NOT number, then xyz*1 = 0 in which case it sorts alphabetically by xyz
if the value does start with a number, then they are sorted by the numerical value, then the alphabetic one

But leading zeros will mess things up, as they will be “ignored”

and if you leave out the “,XYZ” part of ORDER BY
then

will sort in an unknown manner

Thank you Dave.

I did some tests and you are right, if I have 5 XYZ and 5 ABC, without “,XYZ” it lists the first record it found that start with 5.

Now the ‘problem’ I found is that if I have extra records (added to the original list above):

```01.png 0a.png 0.png```

I get:

```0.png 0a.png All good things.png Xojo.png Zulu.png 01.png 1.png```

```All good things.png Xojo.png Zulu.png 0.png 0a.png 01.png```

Other thing is that ‘A-Z’ go before ‘a-z’. If I add ‘all bad things.png’, I get:

```All good things.png Xojo.png Zulu.png all bad things.png```

I’m just testing this to learn, I am not working on, or need, a solution with this information. Just a ‘heads up’ to OP that could find issues if using 0 as first character and using upper/lowercase.

and unless you specify differently… SQL sorts based on CASE, so the results you show are consistent with the methods and data used.

Thank you Dave.

I don’t know how to express my ideas in English, sorry for that, I try my best. What I tried to do with my tests and findings is just ‘extra’ information for new people that could think that the “method” will work in every situation. You already said that leading ZERO will mess up things, I just wanted to show what results one gets if the information has leading zero. Then it occurred to me, the original list all 3 text only titles start with uppercase, what could happen if there are lowercase.

Definitely my tests/posts will not help people with experience, but I do hope that a few newbies could find my tests helpful.

SELECT * FROM tableSort ORDER BY name*0 ASC, name ASC; does not work! Tested with Valentina Studio Pro 8.1 and Xojo 2018 R1 B15.

Normal order:
SELECT * FROM tableSort ORDER BY name*1 ASC, name ASC;
Reverse order:
SELECT * FROM tableSort ORDER BY name*1 DESC, name DESC;

why would you have expected it to “work”… basically

``SELECT * FROM tableSort ORDER BY name*0 ASC, name ASC;``

is logically the same as

``SELECT * FROM tableSort ORDER BY  name ASC;``

since *0 sets the first sort argument to be ZERO for ALL records