Show results from a SQL Union in order by a specific select statement

I came across an issue at work the other day and I could not find much help on how to accomplish it. So again I want to share it here for others to see.

I needed to union a few SQL statements together. Oracle sorting the results by the first field was not helpful. , I wanted the results sorted in order of specific SQL statements. The solution I found that worked for me was to add a column to the query. For example

A simplified version of the table I was working with looked like this

Enclosure Connection1 Strand1 Connection2 Strand2
133977 2014 1 2826 1
133977 2014 2 2826 2
133977 2014 3 2826 3
133977 2004 1 2825 1
133977 2004 2 2825 2
133977 2004 3 2825 3

So my query for this example would be
Select 1 as sortcol,enclosure,connection1,strand1,connection2,strand2
from Connections where connection1 = 2014
Select 2 as sortcol,enclosure,connection1,strand1,connection2,strand2
from Connections where connection1 = 2004 order by strand1;

Since Oracle sorts the results by the first column now it will sort by sortcol. Before it would sort by Enclosure which did not return the results ordered how I wanted them.  Since I was using C# to output these results into a DataGridView I wanted the results already sorted and ready to be put into the grid.

You can of course put sortcol at the end of the select statement and then order by that column.

Select enclosure,connection1,strand1,connection2,strand2,1 as sortcol
from Connections where connection1 = 2014
Select enclosure,connection1,strand1,connection2,strand2,2 as sortcol,
from Connections where connection1 = 2004 order by sortcol,strand1;


Oracle Greatest Function

I came across an issue the other day at work that I thought I could not solve without PL/SQL. I researched because I wanted to get the result using a SQL statement. My issue was that I needed to find the max value between two columns in Oracle. Here is an example table

133977 2014 120 120 2825 86 86
133977 2013 4 4 2826 12 12

I wanted to find the greatest value between HIGH1 and HIGH2. I found this nice function.


This of course returns 120 since 120 > 86.


You can learn more about the greatest function here




Inner Join vs Outer Join

Here I will show the difference between Inner Joins and Outer Joins hence the title Inner Join vs Outer Join.

I have three tables to use for these examples.

Table1: Customers


Table 2: Orders


Table 3: Parts


 Inner Join Vs Outer Join

Inner Join

select fname,lname,ordid from customers  
INNER JOIN Orders ON customers.custid = orders.custid;

This query will show customers that have placed orders. Here is a Venn Diagram of the query. This query returns everything within the middle of the diagram. So custid that is in the customers table as well as the orders table. It will not show any customers that have not ordered and will not show any orders that do not have a customer record.

Venn Diagram of Inner Join(Inner Join vs Outer Join)

Venn Diagram of Inner Join








The result of this query returns

Result of Inner Join

Result of Inner Join







Left Outer Join

select parts.partid,,parts.quantity,ORDID from parts
LEFT OUTER JOIN Orders ON parts.partid = orders.partid;


This query can be shown as

Left Outer Join Venn Diagram

Left Outer Join Venn Diagram








And the result of this query is below

Result of Left Outer Join

Result of Left Outer Join







Here you notice it shows all parts from the parts table whether there has been an order for them or not.


Right Outer Join

select parts.partid,,parts.quantity,ORDID from parts  
RIGHT OUTER JOIN Orders ON parts.partid = orders.partid;

Notice this is the same query as above except I am using Right Outer Join.

Right Outer Join Venn Diagram

Right Outer Join Venn Diagram








Here is the result of the query

Right Outer Join Result

Right Outer Join Result








Old vs New Inner Join/Outer Join syntax

Ask Tom


Oracle Application Express Install

In this post I will discuss the steps needed to install Oracle Application Express 4.2.1.

First you need to make sure you have an Oracle DB installed on your computer. You can get a free version here.

Oracle 11G Express Edition

Here you need a login to the Oracle site. If you don’t have one don’t worry, it’s free to create one! Accept the license agreement then download the DB and install(I may write a blog post about how to install later).

Now to install Oracle Application Express.

Installing Oracle Application Express

Just like the Oracle DB, you need to download it from the Oracle site

Oracle Application Express 4.2

Once downloaded you will need to unzip the folder and move to your desired location. You will notice that all the files  inside the folder are  SQL files and no executable files of any kind. Open a DOS windows and navigate to the location of the apex files. Then run SQL*Plus from the cmd prompt. Login as the sysdba(password was set during DB installation)

Oracle Application Express
After connecting to the Oracle DB you need to run the apexins script.
An example of what parameters to use is shown below

@apexins.sql SYSAUX SYSAUX TEMP /i/

The first parameter is the tablesspace to use for the application user
The second parameter is the table space to use for the files user
The third parameter is the temp tablespace
The fourth parameter is the virtual directory for the application images


After that script gets everything setup the next step is to change the admin password. Again you run a script to accomplish this


Here you will be prompted for a new password. Make sure the password has at least one capital letter, one number and one special character. I think a minimum of 6 characters are required. If you do not meet the password requirements you will not get any notification of it, the script will simply fail.



After this step you are now ready to kick off Oracle Apex and begin making apps. I will cover this part in a separate blog post later.

Here is the Installation Guide made by Oracle for further reading.

Oracle Apex Installation Guide


Oracle Named Loops

When using nested loops in your PL/SQL code it is beneficial to use  Oracle named loops to make the code easier to read.

To name a loop you simply add <<LOOP_NAME>> before the LOOP keyword, then add LOOP_NAME at the end of the END LOOP keyword as shown below



Example using Oracle Named Loops

The code below finds all prime numbers less than 50 while taking advantage of Oracle named loops.


   a number(3);
   b number(3);
   a := 2;
      b:= 2;
         exit WHEN ((mod(a, b) = 0) or (b = a));
         b := b +1;
   IF (b = a ) THEN
      dbms_output.put_line(a || ' is prime');
   END IF;
   a := a + 1;
   exit WHEN a = 50;

As you can see the code is much easier to read when using Oracle named loops. I find it easier to keep track of the begin of end of loops when using nested loops. Other developers that read through your code will thank you too.

For more info on loops you can visit this link