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...

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

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

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...

with DESC, CCA comes first, then all the rows with CBC equivalents

if all rows with CBC are equivalent then why CbC comes last?? didn't get your point. plz explain.

Yeah, that is a good point...
if gives the result
CBC
cBC
cbC
CbC
CCA

then 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?

you know, it's beginning to look as if the order of the CBCs doesn't really matter because they're all equivalent

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

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