Welcome!

Welcome to the official BlackBerry Support Community Forums.

This is your resource to discuss support topics with your peers, and learn from each other.

inside custom component

Adobe AIR Development

Reply
Developer
Posts: 157
Registered: ‎12-16-2010
My Device: Torch 9800, PlayBook
My Carrier: I carry it myself
Accepted Solution

SQLite ORDER BY (sorting) is completely messed up

Or I am messed up.. ;-)

 

My table1 has this data:

 

 

ID Field1
-- ------
1  222
2  aaa
3  bbb
4  ccc
5  AAA
6  BBB
7  CCC
8  111

 

 

When i run the following query

 

SELECT * FROM table1 ORDER BY Field1 ASC

 

 

The result is this:

 

ID Field1
-- ------
8  111
1  222
5  AAA
6  BBB
7  CCC
2  aaa
3  bbb
4  ccc

 

 

So it appears the default sort order is Numbers, Capital Letters alphabetically, Lower Case Lesstes alphabetically....

 

which is odd.... anyone has any suggestions?

----------
If you find this post helpful please "like" it and accept as a solution.
Highlighted
Developer
Posts: 157
Registered: ‎12-16-2010
My Device: Torch 9800, PlayBook
My Carrier: I carry it myself

Re: SQLite ORDER BY (sorting) is completely messed up

should have done some more research before posting.. just googled an answer... but just in case someone else runs into this the default sort order in sqlite is byte based (and A and a have different values) and you need to add COLLATE NOCASE to the end of order by...

 

 

SELECT * FROM table1 ORDER BY Field1 COLLATE NOCASE

 

 

result:

 

 

ID Field1
-- ------
8  111
1  222
2  aaa
5  AAA
3  bbb
6  BBB
4  ccc
7  CCC

 

 

----------
If you find this post helpful please "like" it and accept as a solution.