Real Software Forums
http://forums.realsoftware.com/

ORDER BY Sorts C _before_ A
http://forums.realsoftware.com/viewtopic.php?f=3&t=47876
Page 1 of 1

Author:  Will (CA) [ Tue May 14, 2013 8:58 am ]
Post subject:  ORDER BY Sorts C _before_ A

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

Author:  DaveS [ Tue May 14, 2013 9:13 am ]
Post subject:  Re: ORDER BY Sorts C _before_ A

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

Author:  Will (CA) [ Tue May 14, 2013 9:54 am ]
Post subject:  Re: ORDER BY Sorts C _before_ A

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

Author:  DaveS [ Tue May 14, 2013 10:36 am ]
Post subject:  Re: ORDER BY Sorts C _before_ A

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

Author:  Will (CA) [ Tue May 14, 2013 11:46 am ]
Post subject:  Re: ORDER BY Sorts C _before_ A

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

Page 1 of 1 All times are UTC - 5 hours
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/