Android Question Sorting SQLite database table

[ValDog]

So, I have a text field in a database table the contents of which are in the form "Abc-123". I would like to be able to sort the table by the alphabetic characters and then the numeric characters. For example, if my table record order (for the subject field) looked like:

H-3
Co-60
Co-59
Cs-137
Ba-232

I'd like to be able to sort it to look like:

Ba-232
Co-59
Co-60
Cs-137
H-3

I'd appreciate any suggestions...

 

[mangojack]

ValDog .. have you just tried to sort the table ?

SELECT * FROM MyTable ORDER BY MyTextField

I have not tested this in a SQL table but the values sort correctly when placed in a list and I presume an SQL sort would do the same.

 

[Reviewnow]

SELECT * FROM MyTable ORDER BY MyTextField Does work if you only want a single sorted direction

But lets say you wanted to sort the first set of chrs asc and the second set descending

First Create a Numbers Table

create table numbers(n integer not null, primary key(n asc));

Second
Insert 1,000,000 rows into the numbers table
This only has to be done once a numbers table is very useful for many complicated queries

insert into numbers(n)
select rowid
from (
select 1
from (
  select 0 union select 1 union select 2 union select 3
  union select 4 union select 5 union select 6
  union select 7 union select 8 union select 9
) a, (
  select 0 union select 1 union select 2 union select 3
  union select 4 union select 5 union select 6
  union select 7 union select 8 union select 9
) b, (
  select 0 union select 1 union select 2 union select 3
  union select 4 union select 5 union select 6
  union select 7 union select 8 union select 9
) c, (
  select 0 union select 1 union select 2 union select 3
  union select 4 union select 5 union select 6
  union select 7 union select 8 union select 9
) d, (
  select 0 union select 1 union select 2 union select 3
  union select 4 union select 5 union select 6
  union select 7 union select 8 union select 9
) e, (
  select 0 union select 1 union select 2 union select 3
  union select 4 union select 5 union select 6
  union select 7 union select 8 union select 9
) f

) derived;

Third
Create and run the following sql statement
Replace all occurrences of t2 with your tablename
Replace all occurrences of myfield with your filed name

See imgdb1.jpg

Select myfield
From(
SELECT
  myfield,
  substr( myfield, 1, pos-1) AS first,
  substr( myfield,    pos+1) AS second
FROM (
      SELECT
        t2.myfield,
        numbers.n AS pos
      FROM      t2
      INNER JOIN numbers
      WHERE substr(t2.myfield, numbers.n, 1) = '-'
    ) AS a
ORDER BY first,second
) as b

now change the sort order
order by first,second desc
See Imgdb3.jpg

************************************
The above will also work for sorting customer name fields that contain a single space
Example Bob Smith
Tom Clark
just change this line
WHERE substr(t2.myfield, numbers.n, 1) = '-'
to
WHERE substr(t2.myfield, numbers.n, 1) = ' '

 

[ValDog]

SELECT * FROM MyTable ORDER BY MyTextField Does work if you only want a single sorted direction

But lets say you wanted to sort the first set of chrs asc and the second set descending

First Create a Numbers Table

create table numbers(n integer not null, primary key(n asc));

Second
Insert 1,000,000 rows into the numbers table
This only has to be done once a numbers table is very useful for many complicated queries

insert into numbers(n)
select rowid
from (
select 1
from (
  select 0 union select 1 union select 2 union select 3
  union select 4 union select 5 union select 6
  union select 7 union select 8 union select 9
) a, (
  select 0 union select 1 union select 2 union select 3
  union select 4 union select 5 union select 6
  union select 7 union select 8 union select 9
) b, (
  select 0 union select 1 union select 2 union select 3
  union select 4 union select 5 union select 6
  union select 7 union select 8 union select 9
) c, (
  select 0 union select 1 union select 2 union select 3
  union select 4 union select 5 union select 6
  union select 7 union select 8 union select 9
) d, (
  select 0 union select 1 union select 2 union select 3
  union select 4 union select 5 union select 6
  union select 7 union select 8 union select 9
) e, (
  select 0 union select 1 union select 2 union select 3
  union select 4 union select 5 union select 6
  union select 7 union select 8 union select 9
) f

) derived;

Third
Create and run the following sql statement
Replace all occurrences of t2 with your tablename
Replace all occurrences of myfield with your filed name

See imgdb1.jpg

Select myfield
From(
SELECT
  myfield,
  substr( myfield, 1, pos-1) AS first,
  substr( myfield,    pos+1) AS second
FROM (
      SELECT
        t2.myfield,
        numbers.n AS pos
      FROM      t2
      INNER JOIN numbers
      WHERE substr(t2.myfield, numbers.n, 1) = '-'
    ) AS a
ORDER BY first,second
) as b

now change the sort order
order by first,second desc
See Imgdb3.jpg

************************************
The above will also work for sorting customer name fields that contain a single space
Example Bob Smith
Tom Clark
just change this line
WHERE substr(t2.myfield, numbers.n, 1) = '-'
to
WHERE substr(t2.myfield, numbers.n, 1) = ' '

That's awesome - thank you very much Reviewnow! I'll have to see how this translates into B4A...

 

[Reviewnow]

Your very welcome,

you can even extend that query to do grouping statements on derived table b

Select first as myproduct
From(
SELECT
myfield,
substr( myfield, 1, pos-1) AS first,
substr( myfield, pos+1) AS second
FROM (
SELECT
t2.myfield,
numbers.n AS pos
FROM t2
INNER JOIN numbers
WHERE substr(t2.myfield, numbers.n, 1) = '-'
) AS a
ORDER BY first,second
) as b
group by first order by first

Returns
Ba
Co
Cs
H

Then also a where statement on derived table b

Select myfield
From(
SELECT
myfield,
substr( myfield, 1, pos-1) AS first,
substr( myfield, pos+1) AS second
FROM (
SELECT
t2.myfield,
numbers.n AS pos
FROM t2
INNER JOIN numbers
WHERE substr(t2.myfield, numbers.n, 1) = '-'
) AS a
ORDER BY first,second
) as b
where first = 'Co'
Group by myfield

Returns
Co-59
Co-60

 

[Mahares]

Here is another approach with one query: Create a calculated expression and pad it with zeros and use it to sort either ascending or descending. See tested example and it works:

txt="Select MyField, CASE WHEN LENGTH(MyField)=3  THEN REPLACE(MyField,'-','0000') " _
& " WHEN  LENGTH(MyField)=4  THEN REPLACE(MyField,'-','000') " _
& " WHEN  LENGTH(MyField)=5  THEN REPLACE(MyField,'-','00') " _
& " WHEN LENGTH(MyField)=6  THEN REPLACE(MyField,'-','0') END AS S1 FROM " & DBTableName  & " ORDER BY S1"
Cursor1=SQL1.ExecQuery(txt)
For i=0 To Cursor1.RowCount-1
    Cursor1.Position=i
    Log(Cursor1.GetString("MyField"))  'Displays:  Ba-232  Co-59  Co-60  Cs-137  H-3
Next

 

[ValDog]

Here is another approach with one query: Create a calculated expression and pad it with zeros and use it to sort either ascending or descending. See tested example and it works:

txt="Select MyField, CASE WHEN LENGTH(MyField)=3  THEN REPLACE(MyField,'-','0000') " _
& " WHEN  LENGTH(MyField)=4  THEN REPLACE(MyField,'-','000') " _
& " WHEN  LENGTH(MyField)=5  THEN REPLACE(MyField,'-','00') " _
& " WHEN LENGTH(MyField)=6  THEN REPLACE(MyField,'-','0') END AS S1 FROM " & DBTableName  & " ORDER BY S1"
Cursor1=SQL1.ExecQuery(txt)
For i=0 To Cursor1.RowCount-1
    Cursor1.Position=i
    Log(Cursor1.GetString("MyField"))  'Displays:  Ba-232  Co-59  Co-60  Cs-137  H-3
Next

What is the "S1" you refer to?

Update: Your solution worked very well - thank you! I appreciate the feedback from everyone! The B4A community is amazing!

 

[Mahares]

@ValDog: The S1 is just an alias (arbitrary name for the expression) you give it. You can use anything you want. You can call it Valdog if you want. I just happened to call it S1. The idea was to simply use it to sort your main data.

 

[ValDog]

Gotcha! Thanks again!

 

[KMatle]

This might execute very slow.

Why not put it in two columns (order by c1, c2) and at display time just join both? This will be much faster.