Monday 21 February 2011

Order by + Union All in oracle

In oracle you can not use order by with union all. They dont work together. In this post i will explain you a work around. We will cheat Oracle :-)

--------------------------
Create Table Foo(
Foo_Id Number,
Foo_Name Varchar2(20)
);

Create Table Bar(
Bar_Id Number,
bar_Name Varchar2(20)
);

Insert Into Foo Values(1, 'Fawad');
Insert Into Foo Values(2, 'Nazir');

Insert Into Bar Values(1, 'Mark');
Insert Into bar Values(2, 'Donald');

--Order by and Union All is not allowed together
Select * From Foo Order By Foo_Id desc
Union All
Select * From Bar Order By Bar_Name


--Work around (Cheating SQL :) )
select * from (Select * From Foo Order By Foo_Id desc) a
Union All
select * from (Select * From Bar Order By Bar_Name) b

Enjoy :-)

No comments:

Post a Comment

Azure OpenAI Architecture Patterns & Deployment Patterns

Sharing some useful links that will help customers architect Azure OpenAI solution using the best practices: (1) Azure OpenAI Landing Zone r...