?
Solved

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

Posted on 2014-10-02
7
Medium Priority
?
314 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 2000 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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

Independent Software Vendors: 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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

771 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