Android Question Sorting SQLite database table

Reviewnow said:

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

B4X:

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

B4X:

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

B4X:

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) = ' '

Click to expand...

Mahares said:

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:

B4X:

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

Click to expand...