#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2008
    Posts
    72
    Rep Power
    7

    Order by and sort differences


    I have run into an issue with sorting/ordering field values that contain parathesis. If use use a string like below:

    select * from table where name >='O' order by name asc

    I will get records like this:
    o'hara
    o'neil
    oken
    onet

    If I use the command below on a recordset with the same records

    recordset.sort ="name asc"

    They are ordered like below, as if the apostraphy isn't there
    o'hara
    oken
    onet
    o'neil

    Any idea what can cause this?
    Last edited by wakerunner; April 28th, 2011 at 11:14 PM.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    And what is your problem?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2008
    Posts
    72
    Rep Power
    7
    Originally Posted by clivew
    And what is your problem?
    The problem is why does "order by name asc" sort differently than "sort = name asc"... shouldn't they sort the same?

    "Order by name asc" orders like below
    o'hara
    o'neil
    oken
    onet

    recordset.sort ="name asc" orders like below as if the ' are not there.

    o'hara
    oken
    o'neil
    onet


    Is there a setting I am missing somewhere or maybe something in the connection string that could cause this? I also noticed that once you use the ".sort" command subsequent "order by"'s will also order the same wrong way as if there are no '.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    So this is curiosity , not a problem. OK that is fair.
    Perhaps I am not as familiar with Firebird as I thought; but I am not familiar with your recordset.sort and similar code.
    It is not SQL and I have not come across it in Firebird procedural code.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2008
    Posts
    72
    Rep Power
    7
    Originally Posted by clivew
    So this is curiosity , not a problem. OK that is fair.
    Perhaps I am not as familiar with Firebird as I thought; but I am not familiar with your recordset.sort and similar code.
    It is not SQL and I have not come across it in Firebird procedural code.
    No it is a problem not a curiosity. The problem is if you do "select name from table order by name" then do "recordset.sort = name desc", then "recordset.sort = name asc" it should be in the same order as it was to begin with... it is not.

    recordset.sort is a ADO recordset function. I am using ADO 2.8 with IBphoenix Ole-DB provider. Ibphoenix told me it is probably an issue with Firebird itself, I need to update to 2.5 anyway so I'm going to do that soon and see if it changes anything, if it doesn't I'll just have to make a workaround, or just not use recordset.sort.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    I am reluctant to argue with IBPhoenix; but if the SQL provides the correct result (which it does given ASCII rules) then the problem is with ADO, which has nothing to do with Firebird per se.
    Unless there is some recordset property that addresses this complication, it looks like you will just have to decide which option you want.

    If the SQL(ASCII) sort is required then you will have to issue a fresh SQL request each time.
    If the ADO sort option is required, then don't bother to sort in the SQL SELECT and do it in the recordset as soon as the query completes.

    Clive
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    13
    It's look like ADO removes the ' from the names and sorts them after that.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    Originally Posted by mIRCata
    It's look like ADO removes the ' from the names and sorts them after that.
    I think we all figured that one out.

IMN logo majestic logo threadwatch logo seochat tools logo