Results 1 to 10 of 10

Thread: Order by clause

  1. #1
    Join Date
    Mar 2007
    Posts
    37

    Question Unanswered: Order by clause

    hello everybody. i have a table named employee. where there is just a single column named id which is of type varchar. this is because i just wanted to play with order by clause.

    in the table, i have CCA, cbC, cBC, CbC, CBC. when i queried with select * from employee order by id; the result is as followed-
    CBC
    cBC
    cbC
    CbC
    CCA

    how order by clause worked here, can anybody explain? thanks in advance...

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    for the purpose of the ORDER BY, the collation you are using means that CBC, cBC, cbC, and CbC are all equivalent

    CCA, of course, would come after all of them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    some databases treat upper & lower case the same, some dont, some can do either
    some front ends likewise

    so read the documentation with your db and your front end and see if you can select which order you want.

    for example if you are using microsoft access as your front end 'option compare' may be worth investigating

    MySQL has a mechanism at the database level to handle this sort of issue, and Im sure that will be the same for most other serious servers
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Mar 2007
    Posts
    9
    But if i write select * from employee order by id desc then the result is:
    CCA
    CBC
    cBC
    cbC
    CbC

    i don't have any idea how order by is working here...

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    with DESC, CCA comes first, then all the rows with CBC equivalents
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2007
    Posts
    9
    if all rows with CBC are equivalent then why CbC comes last?? didn't get your point. plz explain.

  7. #7
    Join Date
    Mar 2007
    Posts
    37
    Yeah, that is a good point...
    if
    Code:
    SELECT * FROM employee ORDER BY id;
    gives the result
    CBC
    cBC
    cbC
    CbC
    CCA

    then
    Code:
    SELECT * FROM employee ORDER BY id DESC;
    should provide-
    CCA
    CbC
    cbC
    cBC
    CBC

    why instead of it, the result is like this-
    CCA
    CBC
    cBC
    cbC
    CbC?

    or is it like the order of 4 CBCs doesn't really matter?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you know, it's beginning to look as if the order of the CBCs doesn't really matter because they're all equivalent

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2005
    Posts
    28
    The sort order in databases is related with the Collation of that database.
    For example Latin1_General_CI_AS is a Case Insensitive Accent Sensitive collation.

    For a list of collations in MS SQL Server, you can use fn_helpcollations() function.
    You can read the article http://www.kodyaz.com/content/fnhelpcollations.aspx on fn_helpcollations()

    Eralper
    http://www.kodyaz.com

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If the collation is case-insensitive, then to your database CBC and cbc appear to be the same thing... It doesn't matter which order they appear because for the purposes of comparision they are equal.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •