Jai’s Weblog – Tech, Security & Fun…

Tech, Security & Fun…

  • Jaibeer Malik

    Jaibeer Malik
  • View Jaibeer Malik's profile on LinkedIn
  • Subscribe

  • Feedburner

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 29 other followers

  • Archives

  • Categories

  • Stats

    • 379,210
  • Live Traffic

Tips&Tricks: Oracle Union all query order of records

Posted by Jai on October 25, 2012

The union all query allows to combine the result of multiple select queries. The tip is how it behaves differently in MySQL and Oracle . How you can control the order of records from each select query.

The expected ordering, the default behavior is the order of results,

  • The insert order of records
  • Results from first select first and so on

For mysql, it works well. The records are returned in expected order (may be we just got lucky🙂 )

For oracle, there is no specific order of results, it is random (may be oracle doing extra internal optimization🙂 ) .

To control the order of records returned – Union all ordering recording, check Example

select field1, field2, . field_n
from tables
UNION ALL
select field1, field2, . field_n
from tables;
ORDER BY 1;

The results in each select query will be ordered by first column.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: