Android Question SQL ORDER BY

[sigster]

Hi
I am reading from Sqlite to customlistview how can I use Order By like I show in 1.

1.
in my database I have this record and want to order by like this
De 1x2
De 1x4
De 1x8
De 1x16
De 1x32

2.
the order I get is like this
De 1x16
De 1x2
De 1x32
De 1x4
De 1x7

    Cursor1  = SQL1.ExecQuery2("SELECT * FROM Fl WHERE Fl_ID_t = ? ORDER BY Fl_name ASC", Array As String(ss))

 

[DonManfred]

The Database sorts by the full column.

Do have it sorted differently you need to have
De, 1 and 16
De, 1 and 2
De, 1 and 32
De, 1 and 4
De, 1 and 7
in different columns.

So you can sort by different colums, language, minval, maxval (minval X maxval).

PD: You have a bad databasedesign ;-)

 

[aeric]

Try:

Cursor1 = SQL1.ExecQuery2("SELECT * FROM Fl WHERE Fl_ID_t = ? ORDER BY CAST(SUBSTR(Fl_name, 6) AS Integer) ASC", Array As String(ss))

Note: not tested

 

[DarkoT]

I assume that you have in column data stored as Text... You will need to convert text to number and order by this field... Let's say that column has name "dataTest" ... Here is routine how to convert data to number (=integer):
like this:

select *, cast(substring(dataTest, instr(dataTest,'x') + 1, length(dataTest)) as int) as mydata
from tmpTable
order by mydata;

 

[sigster]

Yes it is stored as Text

Thanks for your replay DonManfres, aeric, DarkoT I will tray this

 

[coldtech]

In SQL Server you can use ORDER BY RIGHT(F1_name, 3) but I do not believe SQLite has that ability.

You could try substr(F1_name, -3) I believe. This assumes there are no values higher than 1x99

 

[Mahares]

select *, cast(substring(dataTest, instr(dataTest,'x') + 1, length(dataTest)) as int) as mydata

You should stay away from using SUBSTRING because the function was added in SQLite version 34.0.0. Instead use SUBSTR. Also, you do not need the Length portion. This is how I would do it:

"SELECT * FROM Fl ORDER BY cast(substr(Fl_name , instr(Fl_name ,'x') + 1) AS INTEGER)")

but I do not believe SQLite has that ability.

Correct. You cannot use RIGHT with SQLite. Using the negative -3 with SBSTR is good also, unless he has column data that is more complex like say, De 1x03 and De 1x4. Here the 1x4 comes ahead of 1x03.

Thanks for your replay DonManfres, aeric, DarkoT I will tray this

This one is for fun only:
Stay away from the letter 'a'. replay should be: reply and tray should be: try. The letter 'a' completely changes the meaning of both words.

 

[LucaMs]

Stay away from the letter 'a'. replay should be: reply and tray should be: try. The letter 'a' completely changes the meaning of both words

DonManfres, instead, is correct ?

 

[JohnJ]

Hi
I am reading from Sqlite to customlistview how can I use Order By like I show in 1.

1.
in my database I have this record and want to order by like this
De 1x2
De 1x4
De 1x8
De 1x16
De 1x32

2.
the order I get is like this
De 1x16
De 1x2
De 1x32
De 1x4
De 1x7

    Cursor1  = SQL1.ExecQuery2("SELECT * FROM Fl WHERE Fl_ID_t = ? ORDER BY Fl_name ASC", Array As String(ss))

Here is something I used some years back on a mainframe. Not sure what results you would get in sqlite.

SELECT aosuh8 FROM srvrpf
order by case when aosuh8 = 'A' then '1'
when aosuh8 = 'I' then '2'
when aosuh8 = 'D' then '3'
when aosuh8 = 'X' then '4'
else '5' end

 

[Mahares]

Not sure what results you would get in sqlite.

Yes, you can do something like that in SQLite:

rs=SQL.ExecQuery($"SELECT * FROM Fl  ORDER BY
    case when F1_name= 'De 1x2' then 1
    when  F1_name='De 1x4' then 2
    when  F1_name='De 1x8' then 3
    when F1_name = 'De 1x16' then 4
    else 5 end
    "$)

But, it becomes cumbersome if you have a multitude of varying data in the column. I think it is best if you involve the SUBSTR function.

 

[RichardN]

I have to agree with @DonManfred. A poor database design makes it very difficult to design logical queries and implement the cumbersome SQL into code. Casting numeric values out of text fields should be added to 'Code smells'

It would be much easier to populate an extra INT field away to use as an 'ORDER BY' field rather than trying to slice strings in SQL queries. (Also a nightmare to debug). You can even save the SQL query to do it as an SQL view in the database.

Keep it simple.

 

[sigster]

Hi
I found this on google and it works

Cursor1  = SQL1.ExecQuery2("SELECT * FROM Fl WHERE Fl_ID_t = ? ORDER by length(Fl_name), Fl_name", Array As String(ss))

 

[Mahares]

I found this on google and it works

I would't get too excited yet. What if you have 2 items: DE 1x16 and De 1x13. The De 1x13 comes up after the DE 1x16. You would need to add COLLATE NOCASE to eliminate the differentiation between upper and lower case or take the SUBSTR approach.

 

[JohnJ]

Yes, you can do something like that in SQLite:

rs=SQL.ExecQuery($"SELECT * FROM Fl  ORDER BY
    case when F1_name= 'De 1x2' then 1
    when  F1_name='De 1x4' then 2
    when  F1_name='De 1x8' then 3
    when F1_name = 'De 1x16' then 4
    else 5 end
    "$)

But, it becomes cumbersome if you have a multitude of varying data in the column. I think it is best if you involve the SUBSTR function.

In Sigster's case he would need to substring on the character following the x and sort on that. That gives him the order that he wants. Of course that assumes that all the data in the table is in that format.

 

[RichardN]

@sigster

Try to think of it this way..... At least 2/3, maybe more, of applications serve as a front-end to some database or other. In B4A it could be SQLite or MySQL.

Before you even start on the app you need to look at your data and decide how you eventually want to do present it. These decisions will dictate the design of your database. Do you really want to store a million repetitions of 'De 1x..' or is there a smarter solution that gives you a much smaller database. When you are using an SQLite manager to initially build your data into a convenient format you start by importing what you already have and manipulate it using basic SQL into fields that are actually useful, not data that you have to manipulate later with complicated SQL code.... or worse still SQL code embedded in B4A.

It is easier to create a field where we can store a sortable reference to the data we wish to sort. I added an integer field called FL_SortOrder.

I populate the field. You may have to do this only one-time if the data package is static.

UPDATE MyTable SET Fl_SortOrder = SUBSTR(Fl_name,INSTR(FL_Name,'x')+1,LENGTH(Fl_Name)-INSTR(Fl_Name,'x'))

That then gives you a convenient field to sort on.

 

[Mahares]

I found this on google and it works

Cursor1 = SQL1.ExecQuery2("SELECT * FROM Fl WHERE Fl_ID_t = ? ORDER by length(Fl_name), Fl_name", Array As String(ss))

That query will not work and will give you an incorrect sort order. Here is the sort order you get if you use it:
De 1x2
De 1x4
De 1x8
DE 1x16
De 1x11
De 1x13
De 1x14
De 1x32
De 1x46
De 1x80
It will only work if you add: COLLATE NOCASE to it. It is often the case when entering a text field, there is a mix of upper and lower cases. When you use your query to sort by the full text column, like you are trying to do, you get an undesirable result.
You are getting a lot of opinions from may members, due to the popularity of SQLite and the vast knowledge about it in this forum. It is up to you to decide what works for you.
I have seen that you were told you don't have the best database design, but you may not have the choice to redesign it or recreate it. Therefore, you manage with what you have and create any query to manipulate the data and make it work without redesigning it.

 

[sigster]

Thanks for the replay

I thing best option is to design the database better like you and another replay did say

I am grateful for the answers I received in this discussion thank you
Sigster

 

[Intelemarketing]

If you are in control of populating the Sort Field, then I am guessing you can set the following
1) Either De or DE
2) Where you have a single digit at the end of the value (like 1x2) you also could prefix the last digit to be 02 rather than just the 2

In the Sort Field you would place the values to be as follows

DE 1x02
DE 1x04
DE 1x08
DE 1x16
DE 1x11
DE 1x13
DE 1x14
DE 1x32
DE 1x46
DE 1x80

(If the last number can be 3 digits for example, then a sort value would be stored, for example, as DE 1x002 and DE 1x080)

Now you have no gymnastics to perform, when sorting based on the Sort Field, which will now sort perfectly in Alphabetic Order

Futhermore, if the data changes in the future, you can always recreate the Sort Field at any time to reflect the change in data rules.

Any day of the week I would rather be debugging Sorting problems using a statement like

Select * from MyTable Order By F1_SortOrder

 

[MrKim]

I agree with others - better design would be a good idea but if you can't, this will work without a temp table:

SELECT F1_ID_t FROM TEST ORDER BY UPPER(SUBSTR(F1_ID_t, 1, 5)), CAST(SUBSTR(F1_ID_t, 6, 20) AS integer);

provided, of course the beginning of the field is always fixed length, as you have shown. UPPER will handle the DE/De problem.