Solved

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

Posted on 2014-10-02
7
288 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
  • 3
  • 3
7 Comments
 
LVL 76

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 142

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 76

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 76

Expert Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

929 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

19 Experts available now in Live!

Get 1:1 Help Now