Click here to Skip to main content
12,403,391 members (72,470 online)
Rate this:
 
Please Sign up or sign in to vote.
i want to union all two queries with second query having order by clause.
examp:

select id,name from table 1
union all
select id,name from table 2 order by name.

in my expected result order the second query result only,not the entire result.
Posted 21-Mar-13 19:41pm
josh-jw16K
Comments
Prakash Thirumoorthy 22-Mar-13 1:45am
   
have u used temporary table?
josh-jw 22-Mar-13 1:51am
   
no.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Hi
Try This....
select id,name from table_1
union all
SELECT T.ID,T.name 
FROM (select TOP 100 PERCENT id,name from table_2 order by name) T

Check the following link also...
Using Derived Tables to Simplify the SQL Server Query Process[^]
Regards,
GVPrabu
  Permalink  
v4
Comments
Maciej Los 22-Mar-13 2:50am
   
Good job, my 5!

Thanks for your comment ;)
gvprabu 22-Mar-13 3:13am
   
Thanks for ur Concern... :-)
josh-jw 22-Mar-13 2:54am
   
ERROR SHOWING
"The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified"
gvprabu 22-Mar-13 3:00am
   
check now.. u can add TOP
josh-jw 22-Mar-13 3:11am
   
How we can predict that only 100 records will be there?if it contains more than 100 , what we will do?
josh-jw 22-Mar-13 3:01am
   
How we can predict that only 100 records will be there?if it contains more than 100 , what we will do?
gvprabu 22-Mar-13 3:11am
   
it is 100 % not only 100 Records... if u have 1000 Records in that query.. it will return 1000 Rows not 100 Rows...
josh-jw 22-Mar-13 3:14am
   
YES.i tested correct.thanks a lot gvprabu.
gvprabu 22-Mar-13 3:15am
   
ok fine.. always welcome ... :-)
josh-jw 22-Mar-13 3:23am
   
i have a doubt. the inner selected query with order by is working fine. when running outer query
SELECT T.ID,T.name
FROM (select TOP 100 PERCENT id,name from table_2 order by name) T

records are not in order.
gvprabu 22-Mar-13 3:25am
   
Send the table Script and Sample Data....
josh-jw 22-Mar-13 3:59am
   
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[table1](
[id] [bigint] NULL,
[name] [nchar](10) NULL
) ON [PRIMARY]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[table2](
[id] [bigint] NULL,
[name] [nchar](10) NULL
) ON [PRIMARY]
josh-jw 22-Mar-13 4:00am
   
table 1
1 minu
2 eric
3 levin
4 zod

table 2
1 we
josh-jw 22-Mar-13 4:15am
   
?
gvprabu 22-Mar-13 4:16am
   
I went for lunch... I will check and tel u ASAP.
josh-jw 22-Mar-13 4:31am
   
ok
gvprabu 22-Mar-13 4:32am
   
Sorry yar... Its not making Order by based on Table2...
Check the link...http://www.mssqltips.com/sqlservertip/1387/joining-data-using-union-and-union-all-in-sql-server/
ORDER BY and COMPUTE clauses can only be issued for the overall result set and not within each individual result set

So go for Temp table... :-(
josh-jw 22-Mar-13 4:38am
   
ok . :)
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

hi,

use the following query



create table #temp (ID int, name varchar(50))
 
Insert into #temp   select id,name  from table1
Insert into #temp   select id,name  from table2 order by ID desc
 
select * from #temp
drop table #temp


it may help you..

Regards,
Prakash.T
  Permalink  
v2
Comments
josh-jw 22-Mar-13 2:12am
   
thanks for reply.but i would like a query without using a temperory table.is there any other way to do this functionality?
Prakash Thirumoorthy 22-Mar-13 2:22am
   
I think, may be there is no option for that...
gvprabu 22-Mar-13 2:42am
   
we have option... use Derived tables like as my Solution :-)
Prakash Thirumoorthy 22-Mar-13 2:51am
   
nice :-)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160721.1 | Last Updated 22 Mar 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100