New topic: ORDER BY Sorts C _before_ A
<http://forums.realsoftware.com/viewtopic.php?t=47876> Page 1 of 1 [ 5 posts ] Previous topic | Next topic Author Message Will (CA) Post subject: ORDER BY Sorts C _before_ APosted: Tue May 14, 2013 8:58 am 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 DaveS Post subject: Re: ORDER BY Sorts C _before_ APosted: Tue May 14, 2013 9:13 am Joined: Sun Aug 05, 2007 10:46 am Posts: 4870 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 Will (CA) Post subject: Re: ORDER BY Sorts C _before_ APosted: Tue May 14, 2013 9:54 am 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 DaveS Post subject: Re: ORDER BY Sorts C _before_ APosted: Tue May 14, 2013 10:36 am Joined: Sun Aug 05, 2007 10:46 am Posts: 4870 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 Will (CA) Post subject: Re: ORDER BY Sorts C _before_ APosted: Tue May 14, 2013 11:46 am 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. Thank you for all you help! -Will Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 5 posts ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
