how to order correctly in sqlite?

[doctorized]

We have a db table and the first column (named id) has values like:

Code: Select all

A1
A2
A3
A4
...
A20
B1
B2
...
B20

And a query like:

Code: Select all

select * from table order by id

returns:

Code: Select all

A1
A10
A11
A12
...
A19
A2
A20
B1
B10
B11
...
B19
B2
B20

how can I get the correct order 1-20 for every letter? I know values like:

Code: Select all

A01
A02
etc

are preferable, but is there a solution without leading zeros?

[Kiffi]

Code: Select all

UseSQLiteDatabase()

OpenDatabase(0, ":memory:", "", "", #PB_Database_SQLite)

DatabaseUpdate(0, "Create Table TestTable (TestField)")

DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('B1')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('B3')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('B10')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('B2')")

DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('A1')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('A3')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('A10')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('A2')")

DatabaseQuery(0, "Select * From TestTable Order By Substr(TestField, 1, 1), Cast(Substr(TestField, 2, 10) As Int)")

If DatabaseError()
  Debug DatabaseError()
EndIf

While NextDatabaseRow(0)
  Debug GetDatabaseString(0, 0)
Wend

[doctorized]

Actually helped as every prefix is stored in a separate table. Thanx a lot!!

[normeus]

Thank you Kiffi, and any one else who takes time to type a full example. Kiffi typed all that data so he can show you proof of concept, the databaseQuery() command where he splits the id then sorts by the two parts:

Code: Select all

DatabaseQuery(0, "Select * From TestTable Order By Substr(TestField, 1, 1), Cast(Substr(TestField, 2, 10) As Int)")

Thank you.
Norm.

[doctorized]

Kifi's solution solved one of my problems. I have a second query where the results have no known prefix like:

Code: Select all

ABC2
ABC10
ABCD5
ABCD10
ABCDEF1
ABCDEF10
ETC

How can we order in such a case?

[normeus]

I am using PB 6.02 LTS on windows 11 with PBs builtin MySql. For some unGodly reason this code seems to work. I went back to the Docs and Ltrim or Rtrim do not have parameters. I would load The latest MySql LIB and use REGEXP instead which is not available on my PBs version.

Code: Select all

UseSQLiteDatabase()

OpenDatabase(0, ":memory:", "", "", #PB_Database_SQLite)

DatabaseUpdate(0, "Create Table TestTable (TestField)")

DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('B1')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('B3')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('BC10')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('B2')")

DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('A1')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('A3')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('A20')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('A10')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('A2')")

DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('ABC2')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('ABC10')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('ABCD5')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('ABCD10')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('ABCDEF1')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('ABCDEF10')")
;DatabaseQuery(0, "Select * From TestTable Order By Substr(TestField, 1, 1), Cast(Substr(TestField, 2, 10) As Int)")

DatabaseQuery(0, "Select * ,Rtrim(TestField, '1234567890') AS Part1,ltrim(TestField, 'ABCDEFGH') AS Part2 From TestTable Order By Part1 ASC,CAST(Part2 As Int) ASC")

If DatabaseError()
  Debug DatabaseError()
EndIf

While NextDatabaseRow(0)
  Debug GetDatabaseString(0, 0)
Wend

Norm.

[Marc56us]

LTRIM and RTRIM in MySQL only remove spaces.
Use TRIM to remove other chars

Code: Select all

 TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)  

[doctorized]

Thank you very much! One last question. What about having words like the following?

Code: Select all

TEST.ACK.1
TEST.ACK.10
PROD.MX.2
PROD.MX.10
PROD.10
PROD.2
LOG.DAT.10
LOG.DAT.5

[normeus]

Again, I've used the Ltrim() code since 8 years ago with 2 parameters. More than likely it is a glich on the version of MySql used with PB because the MySQL manual has only one parameter. Use with caution. ( or better yet download the latest MySQL dll and use REGEXP )
For the sample code, you only need to expand the "Ltrim" part to the whole alphabet.

Code: Select all

UseSQLiteDatabase()

OpenDatabase(0, ":memory:", "", "", #PB_Database_SQLite)

DatabaseUpdate(0, "Create Table TestTable (TestField)")

DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('B1')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('B3')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('BC10')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('B2')")

DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('A1')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('A3')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('A20')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('A10')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('A2')")

DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('ABC2')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('ABC10')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('ABCD5')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('ABCD10')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('ABCDEF1')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('ABCDEF10')")

DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('TEST.ACK.1')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('TEST.ACK.10')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('PROD.MX.2')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('PROD.MX.10')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('PROD.10')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('PROD.2')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('LOG.DAT.06')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('LOG.DAT.16')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('LOG.DAT.10')")
DatabaseUpdate(0, "Insert Into TestTable (TestField) Values ('LOG.DAT.5')")
;DatabaseQuery(0, "Select * From TestTable Order By Substr(TestField, 1, 1), Cast(Substr(TestField, 2, 10) As Int)")

DatabaseQuery(0, "Select * ,Rtrim(TestField, '1234567890') AS Part1,ltrim(TestField, 'ABCDEFGHHIJKLMNOPQRSTUVWXYZ.') AS Part2 From TestTable Order By Part1 ASC,CAST(Part2 As Int) ASC")

If DatabaseError()
  Debug DatabaseError()
EndIf

While NextDatabaseRow(0)
  Debug GetDatabaseString(0, 0)
Wend

Norm.