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

Posted on 2014-10-02
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 
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!
Question by:vistamed
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
LVL 77

Accepted Solution

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)

Author Closing Comment

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:


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:
But in fact it sorts like this:

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 
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!
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...
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now


Author Comment

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!
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.

Author Comment

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!
LVL 77

Expert Comment

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

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

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…
This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

688 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