Solved

How does ORDER BY work?

Posted on 2012-03-26
17
260 Views
Last Modified: 2012-03-27
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
Comment
Question by:midfde
  • 9
  • 4
  • 2
  • +1
17 Comments
 
LVL 5

Accepted Solution

by:
Becky earned 500 total points
ID: 37769233
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
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 37769244
What is the datatype of the column you are searching on, and what is the collation of the column you are searching on?
0
 
LVL 1

Author Comment

by:midfde
ID: 37769338
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
 
LVL 1

Author Comment

by:midfde
ID: 37769357
SELECT DATABASEPROPERTYEX('dev:Expert', 'Collation') SQLCollation

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

(1 row(s) affected)

Open in new window

0
 
LVL 1

Author Comment

by:midfde
ID: 37769466
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
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 37769483
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37769559
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37769633
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 1

Author Comment

by:midfde
ID: 37770700
>>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
 
LVL 1

Author Comment

by:midfde
ID: 37770740
(5) N'a' > N'-'
(6) N'a' < N'-'
(7) N'Z' > N'-'
(8) N'Z' < N'-'

Open in new window

0
 
LVL 1

Author Comment

by:midfde
ID: 37770890
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37770970
>>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
 
LVL 1

Author Comment

by:midfde
ID: 37771101
>>...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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37771166
>> 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
 
LVL 1

Author Comment

by:midfde
ID: 37771252
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
 
LVL 1

Author Closing Comment

by:midfde
ID: 37771963
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
 
LVL 5

Expert Comment

by:Becky
ID: 37774916
Sure thing   :)
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now