Sunday, October 29, 2006

Merge Individual Query Result in a sorted fashion

It is not possible to apply Order by Clause while using UNION Query, when we want to sort the individual query result and then want to merge the result data

Case: If we want to sort the individual query result and then want to merge the result data it is not possible to do so. Read the following article to see how it is possible.

Example:

Problem
--------
select categoryid,categoryname
from categories
order by 2
UNION
select productid,productname
from products
order by 2

Error
------
--It will give me following error
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'UNION'.


Partial Solution
------------------
select categoryid,categoryname
from categories
UNION
select productid,productname
from products
order by 2

--Problem with Partial Solution
It will sort all the result data by column 2., It is general sorting which is applied when we want to sort all the result data.

But what if we want to sort the individual query result and then want to merge the result data.

Solution
----------

select * from
(
select categoryid,categoryname, 1 as myOrder
from categories
UNION
select productid,productname, 2 as myOrder
from products
)
myTable
order by myTable.myOrder

So finally here the result query will display the data sorted by category and then by product. and the result data is shown.

1 comment:

Anonymous said...

You are a genuis!

Most Recent Post

Subscribe Blog via Email

Enter your email address:



Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
Page copy protected against web site content infringement by Copyscape