by **chrisb** » Fri Mar 19, 2021 7:21 pm

every character has a numeric value referred to as ASCII 'American Standard Code'.

numbers have ASCII codes between 48(0) & 57(9)

upper case letters have ASCII codes between 65(A) & 90(Z)

lower case letters have ASCII codes between 97(a) & 122(z)

A > a = false because ASCII values are compared and 65 is less than 97

A < a = true because 65 is less than 97

when sorting fields the database checks the field type & orders rows accordingly:

if our field type is text then numbers precede upper case letters & upper case letters precede lower case letters (1 precedes Z & Z precedes a)

if our field type is numeric then lessor values precede greater values (5 precedes 12)

if we cast numeric values as text then they get treated as text (12 precedes 5)

you can visualise this as ASCII 12 = (49 & 50), 5 = (53) so 12 will precede 5 because 49 is less than 53

as you yourself discovered after converting numbers to text: order doesn't work correct (1, 10, 11 ... 2, 20, 21...)

the solution is to precede values with zeros which using your example gives us exactly what we are looking for: '01', '02', '10', '11', '20', '21'

5 is it the length of TinyInt?

not true, 5 is the length of smallint, max value(32,767), two bytes.

that is why i precede the value with four zeros ('0000' || smallint).

if our smallint has a value of one we get the text string '00001'

if our smallint has a value of 32,767 we get the text string '000032767'

the function RIGHT is used to extract the rightmost five characters from our concatenated string ('00001' or '32767') & that's how we accurately sort numbers as text.

open office 4.1.8 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.2 (Split) on Windows 10