Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Fri May 26, 2017 9:33 pm
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 5 posts ] 
Author Message
 Post subject: ORDER BY Sorts C _before_ A
PostPosted: Tue May 14, 2013 8:58 am 
Offline

Joined: Tue Feb 06, 2007 9:59 pm
Posts: 6
I am writing a web app which contains patient data

WebListBoxes cannot sort, so I have to use order by to sort the patients by their ID code.

rs = db.SQLSelect("SELECT * FROM Patient")
gives the following order in the list box:

pKey PatID
1 bon601
2 bon601b
3 cro601c
4 wer455a
5 alp901
6 cro602
7 a
8 bar101
9 cun401
10 ddd102
11 eee103
12 ccc104
13 cac106


rs = db.SQLSelect("SELECT * FROM Patient ORDER BY PatID")
returns the following in the list box:

pkey PatID
13 cac106
12 ccc104
3 cro601c
6 cro602
9 cun401
7 a
5 alp901
1 bon601
2 bon601b
8 bar101
10 ddd102
11 eee103
4 wer455a


Note that this is NOT proper alphabetical order! C is coming before A and BO before BA, etc.

Is this a bug in SQLite? or am I using ORDER BY incorrectly? Documentation on ORDER BY is sketchy

Thanks -Will


Top
 Profile  
 
 Post subject: Re: ORDER BY Sorts C _before_ A
PostPosted: Tue May 14, 2013 9:13 am 
Offline
User avatar

Joined: Sun Aug 05, 2007 10:46 am
Posts: 4931
Location: San Diego, CA
1) are you sure that some PatID values do NOT begin with a non-printable character?
2) I am assuming you are showing TWO fields here pKey (the number) and PatID the character string?
3) what database engine?
4) Another assumption.... that you are inserting in the LB in a loop, reading from a returned recordset

_________________
Dave Sisemore
iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1
Note : I am not interested in any solutions that involve custom Plug-ins of any kind


Top
 Profile  
 
 Post subject: Re: ORDER BY Sorts C _before_ A
PostPosted: Tue May 14, 2013 9:54 am 
Offline

Joined: Tue Feb 06, 2007 9:59 pm
Posts: 6
I am certain that there are no non-printing characters in the column PatID

Yes, I am showing two fields, pKey is the integer primary key and PatID is the patient ID code

I am using the RealSQLDatabase engine

I am inserting in the ListBox in a loop, reading from a returned record set.

thanks -Will


Top
 Profile  
 
 Post subject: Re: ORDER BY Sorts C _before_ A
PostPosted: Tue May 14, 2013 10:36 am 
Offline
User avatar

Joined: Sun Aug 05, 2007 10:46 am
Posts: 4931
Location: San Diego, CA
and

a) YES you are using it correctly
b) NO there is no bug (I've been using it for years and never have seen this)

your code looks something like this??

listbox1.deleteallrows
rs = db.SQLSelect("SELECT * FROM Patient ORDER BY PatID")
while not rs.eof
listbox1.addrow rs.field("PatID").stringvalue
rs.movenext
wend

_________________
Dave Sisemore
iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1
Note : I am not interested in any solutions that involve custom Plug-ins of any kind


Top
 Profile  
 
 Post subject: Re: ORDER BY Sorts C _before_ A
PostPosted: Tue May 14, 2013 11:46 am 
Offline

Joined: Tue Feb 06, 2007 9:59 pm
Posts: 6
I had to write a routine that searched for non-printing chars, turns out a bug elsewhere in the program
was introducing them. :oops: Thank you for all you help! -Will


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 5 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group