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

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!
vistamedAsked:
Who is Participating?
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.

slightwv (䄆 Netminder) Commented:
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

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
vistamedAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

vistamedAuthor Commented:
Hi Guy,  you're right, it was in some other place. My apologies for the information in the original question being incorrect. Sorry!
0
slightwv (䄆 Netminder) Commented:
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
vistamedAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
lol...  No problem.  We've ALL had these types of moments...
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
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.