• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

How does ORDER BY work?

Please help me (very experienced SQL Server user) to find an answer to my following question in BOL:
Why "--F..." is not at the top of returned row set? Please see the image.
-.jpg
0
midfde
Asked:
midfde
  • 9
  • 4
  • 2
  • +1
1 Solution
 
BeckyCommented:
You're expecting those dashes to be sorting according to your view of the alphabet and where special characters come in to play.  You want to make use of SQL collation and pick the collation sort type.   If you were to use Latin1 collation the dash would get sorted above letters of the alphabet.  I'm willing to bet your sort is Unicode which ignores hyphens and that's what's being used by default.

http://support.microsoft.com/kb/322112
0
 
Dale BurrellDirectorCommented:
What is the datatype of the column you are searching on, and what is the collation of the column you are searching on?
0
 
midfdeAuthor Commented:
Both tables were imported from MS Access in order to use FULL JOIN. All Wizard defaults were accepted. The purpose was just to view quick SELECT result as a basis for Venn diagram - like model.
-.jpg
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
midfdeAuthor Commented:
SELECT DATABASEPROPERTYEX('dev:Expert', 'Collation') SQLCollation

SQLCollation
----------------------------------------------------
SQL_Latin1_General_CP1_CI_AS

(1 row(s) affected)

Open in new window

0
 
midfdeAuthor Commented:
select db_name()
------------------------
dev:Expert

		select 'A' col
union	select '--F'
union	select 'B'
union	select 'Z'
union	select 'a'
order by col desc
col
----
Z
B
a
--F
		select 'A' col
union	select '--F'
union	select 'B'
union	select 'Z'
union	select 'a'
order by col 

col
----
--F
a
B
Z

Open in new window

0
 
Dale BurrellDirectorCommented:
Have you told us yet what the datatype of your column is? And the collation of your column can be different to the database.

Try the following and see if it works:

order by convert(nvarchar,Column) collate SQL_Latin1_General_CP1_CI_AS

Open in new window

0
 
Anthony PerkinsCommented:
Both tables were imported from MS Access
I suspect you just answered your own question:  You are using unicode data types (nchar(nvarchar) instead of ASCII (char and varchar)
0
 
Anthony PerkinsCommented:
Here is your exact sample using unicode:
select N'A' col
union      select N'--F'
union      select N'B'
union      select N'Z'
union      select N'a'
order by col desc

Z
--F
B
a

Select N'A' col
union      select N'--F'
union      select N'B'
union      select N'Z'
union      select N'a'
order by col

a
B
--F
Z

See the difference?  Incidentally you may want to explore the difference between UNION and UNION ALL
0
 
midfdeAuthor Commented:
>>Have you told us yet what the datatype of your column is?
Yes, please see the image in Posted on 2012-03-26 at 20:37:31ID: 37769338

>>...answered your own question

I was asking for help in finding an answer to my following question in BOL:...    2012-03-26 at 19:46:51ID27649595

To rephrase: what of the below is true in the described case and why ORDER BY does not honor these inequalities?
(1) 'a' > '-'
(2) 'a' < '-'
(3) 'Z' >'-'
(4) 'Z' <'-'

Open in new window

All data types and collations I described above.
0
 
midfdeAuthor Commented:
(5) N'a' > N'-'
(6) N'a' < N'-'
(7) N'Z' > N'-'
(8) N'Z' < N'-'

Open in new window

0
 
midfdeAuthor Commented:
select 'N''a'' > N''-''', case when N'a' > N'-' then 'true' else 'false' end
union 
select 'N''a'' < N''-''', case when N'a' < N'-' then 'true' else 'false' end
union 
select 'N''Z'' > N''-''', case when N'a' > N'-' then 'true' else 'false' end
union 
select 'N''Z'' < N''-''', case when N'a' < N'-' then 'true' else 'false' end
----------- -----
N'a' < N'-' false
N'a' > N'-' true
N'Z' < N'-' false
N'Z' > N'-' true

(4 row(s) affected)

Open in new window

0
 
Anthony PerkinsCommented:
>>Yes, please see the image in Posted on 2012-03-26 at 20:37:31ID: 37769338<<
There are no data type shown.  However, from your output and from the example I posted you must be using unicode hence the result.

>>I was asking for help in finding an answer to my following question <<
This has been stated many times already, but lets try it again:  You appear to be using unicode data types.

>>what of the below is true in the described case and why ORDER BY does not honor these inequalities?<<
I am sorry you fail to understand.  Hopefully someone else is better at explaining it, I thought the example I posted was self-evident.

Good luck.
0
 
midfdeAuthor Commented:
>>...you appear ...

I am not talking about me but about very specific and simple SQL statement and its unexpected result.
On one hand SQL Server engine recognizes that N'a' > N'-' is TRUE, i.e. "-" precedes 'a' On the other hand it places '--F...' after 'A..' and before 'frame' as result of my query. Please see the image (yes, please see, and do not say "There are no data") in my initial message.
The question is this simple: "Where in BOL can I find explanation of this strange behavior?".
I'd like to ascertain I never face this abnormality again, despite of my level of understanding of UNION ALL [that has nothing to do with the issue in question].
0
 
Anthony PerkinsCommented:
>> its unexpected result.<<
It is not unexpected.  Quite the opposite, as I have shown in my example.

>> Please see the image (yes, please see, and do not say "There are no data") i<<
I did not say that.  Please re-read what I stated.  Here is the relevant part: "There are no data type shown."  As in what data types you are using.

You know all of this can be easily resolved if you took the time to post the schema (CREATE TABLE) for your tables [Device Specs Sub] and [Device Specs Sub chlr] .

But for now, I do not have any more time, so I am going to have to wish you good luck (again).
0
 
midfdeAuthor Commented:
Hopefully anybody else among Experts can read, understand, discern nvarchar Data Type (mentioned twice)  on the quoted picture, and answer my question.
Thanks, acperkins anyway. You seem to know answers to many questions, but I need it for my specific one about unpredictable location of "--F..." row.
0
 
midfdeAuthor Commented:
Thanks greensunie.
Key words are: "...ignores hyphens and that's what's being used by default"
...And this is after a decade of... What the heck?
0
 
BeckyCommented:
Sure thing   :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 9
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now