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
UNION
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
UNION
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

FEAT_ID FEAT1 LOW1 HIGH1 FEAT2 LOW2 HIGH2
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.


SELECT GREATEST(HIGH1,HIGH2) FROM SPLICE_CONNECT WHERE FEAT_ID = 133977 AND FEAT1 = 2014 AND HIGH1 = 120;

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

CUSTID
FNAME
LNAME
ST_ADDRESS
CITY
STATE
HOME_PHONE
EMAIL

Table 2: Orders

ORDID
CUSTID
PARTID
QUANTITY

Table 3: Parts

PARTID
NAME
DESCRIPTION
QUANTITY

 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.name,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.name,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