Databases
StackOverflow2013
Postsdbo
POordering the results of a full join in sql
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POordering the results of a full join in sql
    text
    copied!<p>I have 2 tables on which I am doing a full outer join.Both the tables have a timestamp column. I want the result of the join to be ordered by the timestamp. such that say table a has rows </p> <h2>Table A</h2> <pre><code>X timestamp 92408413 12/22/2011 11:23:36 AM 92408414 12/22/2011 11:23:40 AM 92408417 12/22/2011 11:23:45 AM 92408419 12/22/2011 11:23:49 AM 92408423 12/22/2011 11:23:55 AM 92408424 12/22/2011 11:23:57 AM 92408426 12/22/2011 11:24:01 AM 92408427 12/22/2011 11:24:09 AM 92408430 12/22/2011 11:24:33 AM 92408435 12/22/2011 11:25:03 AM 92408437 12/22/2011 11:25:13 AM 92408497 12/22/2011 11:31:09 AM 92408498 12/22/2011 11:31:10 AM 92408499 12/22/2011 11:31:12 AM </code></pre> <h2>table B</h2> <pre><code>Y X timestamp 5020926963 92408430 12/22/2011 11:24:33 AM 5020926968 92408435 12/22/2011 11:25:03 AM 5020926970 92408435 12/22/2011 11:25:05 AM 5020926972 92408437 12/22/2011 11:25:14 AM 5020926981 -1 12/22/2011 11:31:09 AM 5020926982 -1 12/22/2011 11:31:10 AM 5020926984 92408499 12/22/2011 11:31:12 AM </code></pre> <p>Now if my query looks something like </p> <pre><code>SELECT * FROM tablea FULL OUTER JOIN tableb ON tablea.x = tableb.x AND tablea.w = tableb.w WHERE tablea.x = 'somevalue' OR tableb.x = 'somevalue' ORDER BY tablea.timestamp, tableb.timestamp </code></pre> <p>The result of the query is :</p> <pre><code>92408413 12/22/2011 11:23:36 AM 92408414 12/22/2011 11:23:40 AM 92408417 12/22/2011 11:23:45 AM 92408419 12/22/2011 11:23:49 AM 92408423 12/22/2011 11:23:55 AM 92408424 12/22/2011 11:23:57 AM 92408426 12/22/2011 11:24:01 AM 92408427 12/22/2011 11:24:09 AM 92408430 12/22/2011 11:24:33 AM 5020926963 12/22/2011 11:24:33 AM 92408435 12/22/2011 11:25:03 AM 5020926968 12/22/2011 11:25:03 AM 92408435 12/22/2011 11:25:03 AM 5020926970 12/22/2011 11:25:05 AM 92408437 12/22/2011 11:25:13 AM 5020926972 12/22/2011 11:25:14 AM 92408497 12/22/2011 11:31:09 AM 92408498 12/22/2011 11:31:10 AM 92408499 12/22/2011 11:31:12 AM 5020926984 12/22/2011 11:31:12 AM 5020926981 12/22/2011 11:31:09 AM 5020926982 12/22/2011 11:31:10 AM </code></pre> <p>Now, this query first orders by TableA's tiemstamp and then TableB's. I want the result to be odered by timestamp(in order of time).So basically ordering by (TableA.timestamp + TableB.timestamp). As you can see there is a row in Table A where the timestamp is ' 92408497 12/22/2011 11:31:09 AM' I want the next row in the result to have a column from Table B which has the same timestamp ' 5020926981 -1 12/22/2011 11:31:09 AM'</p> <p>I am on a Oracle 11.2 database.</p> <p>Thanks,</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload