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
LVL 1
midfdeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.