PureBasic Forums - English

http://www.purebasic.com
https://www.purebasic.fr/english/

how to order correctly in sqlite?

https://www.purebasic.fr/english/viewtopic.php?t=82578

Page 1 of 1

how to order correctly in sqlite?

Posted: Mon Oct 02, 2023 10:51 pm
by 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?

Re: how to order correctly in sqlite?

Posted: Mon Oct 02, 2023 11:19 pm
by 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

Re: how to order correctly in sqlite?

Posted: Mon Oct 02, 2023 11:39 pm
by doctorized
Actually helped as every prefix is stored in a separate table. Thanx a lot!!

Re: how to order correctly in sqlite?

Posted: Mon Oct 02, 2023 11:44 pm
by 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.

Re: how to order correctly in sqlite?

Posted: Tue Oct 03, 2023 12:05 am
by 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?

Re: how to order correctly in sqlite?

Posted: Tue Oct 03, 2023 2:11 am
by 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.

Re: how to order correctly in sqlite?

Posted: Tue Oct 03, 2023 7:25 am
by 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)

Re: how to order correctly in sqlite?

Posted: Tue Oct 03, 2023 7:42 pm
by 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

Re: how to order correctly in sqlite?

Posted: Tue Oct 03, 2023 11:34 pm
by 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.
All times are UTC+01:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited