Solved

How do I order the results of a Union query in Oracle SQL?

Posted on 2014-10-02
7
302 Views
Last Modified: 2014-10-02
Hi, I'm trying to run a simple select statement. Or at least, I THOUGHT it would be simple...
Here's the code:
select * from 
(select email from contact 
union 
select email from vendor_contact
)
order by email

Open in new window

I was hoping to get the two lists of email addresses, combined and sorted in alphabetical order.
But no matter what I've tried, the result is not ordered properly - the result always appears as though I hadn't entered any 'ORDER BY' statement at all!
The result lists the contact email addresses from A to Z, then the Vendor_contact email addresses from A to Z.
Can anybody tell me what I'm doing wrong please? I'm using Oracle 11gR2, in case that matters.

Thank you!
0
Comment
Question by:vistamed
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40356766
What you posted should be correct.

The only thing I can think of is the case.  A lower case a is different than an upper case A.

Maybe try:
...
order by lower(email)
0
 

Author Closing Comment

by:vistamed
ID: 40356827
Hi slightwv,
Thank you! You gave me the correct answer despite me being stupid and asking the wrong question...

The resulting list I was getting was NOT the contact list A-Z, followed by the vendor_contact list A-Z.

What it actually was, was:

A
B
C
.....
X
Y
Z
a
b
c
....
x
y
z

So as far as Oracle is concerned, Capital B comes before small A when it comes to sorting!
I would have expected it to sort like this:
AaBbCcDd......XxYyZz
But in fact it sorts like this:
ABCD...XYZabc...xyz

Which is a bit daft in my book, but at least now I know!

So when I change the code to
select * from 
(
select lower(email) blah from contact 
union 
select lower(email) blah from vendor_contact 
) 
order by blah

Open in new window


then I get the nicely ordered list I was hoping for.

Thank you and sorry for the misleading information in the original question!
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40356834
I confirm it should be correct, though I would use UNION ALL eventually, unless you want to explicitly have duplicates to be removed
but this should not influence the ordering

so the issue must be on some other "place"... either you don't show "all" of it, or it's in the data, things like leading spaces or case sensitive issue...
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:vistamed
ID: 40356838
Hi Guy,  you're right, it was in some other place. My apologies for the information in the original question being incorrect. Sorry!
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40356839
No problem.  I figured it was a case issue.

I would probably still move the lower to the order by like my example and leave the email addresses as they were provided.

>>So as far as Oracle is concerned, Capital B comes before small A when it comes to sorting!

Actually it is ASCII not Oracle.  Any system will sort that way.

www.asciitable.com
0
 

Author Comment

by:vistamed
ID: 40356862
Hi slightwv,
oh dear, I'm fairly showing my ignorance to the world today, aren't I...? embarrassed...Thanks for not pointing and laughing! :)
And thanks for making me realise that you can use functions like LOWER in an ORDER BY statement too. Every day's a school day for me!
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40356868
lol...  No problem.  We've ALL had these types of moments...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MULTIPLE DATE QUERY 15 103
Deleting Rows from an Oracle Database - Performance 19 75
oracle DR - data guard failover. 18 60
Password_rules_securitty.. 12 34
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

733 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