Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
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 20:41pm
josh-jw11.1K
Comments
Prakash Thirumoorthy at 22-Mar-13 1:45am
   
have u used temporary table?
josh-jw at 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 at 22-Mar-13 2:50am
   
Good job, my 5!
 
Thanks for your comment ;)
gvprabu at 22-Mar-13 3:13am
   
Thanks for ur Concern... :-)
josh-jw at 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 at 22-Mar-13 3:00am
   
check now.. u can add TOP
josh-jw at 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 at 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 at 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 at 22-Mar-13 3:14am
   
YES.i tested correct.thanks a lot gvprabu.
gvprabu at 22-Mar-13 3:15am
   
ok fine.. always welcome ... :-)
josh-jw at 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 at 22-Mar-13 3:25am
   
Send the table Script and Sample Data....
josh-jw at 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 at 22-Mar-13 4:00am
   
table 1
1 minu
2 eric
3 levin
4 zod
 
table 2
1 we
josh-jw at 22-Mar-13 4:15am
   
?
gvprabu at 22-Mar-13 4:16am
   
I went for lunch... I will check and tel u ASAP.
josh-jw at 22-Mar-13 4:31am
   
ok
gvprabu at 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 at 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 at 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 at 22-Mar-13 2:22am
   
I think, may be there is no option for that...
gvprabu at 22-Mar-13 2:42am
   
we have option... use Derived tables like as my Solution :-)
Prakash Thirumoorthy at 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
0 George Jonsson 215
1 Kornfeld Eliyahu Peter 169
2 Zoltán Zörgő 139
3 PIEBALDconsult 130
4 OriginalGriff 120
0 OriginalGriff 6,165
1 DamithSL 4,658
2 Maciej Los 4,107
3 Kornfeld Eliyahu Peter 3,649
4 Sergey Alexandrovich Kryukov 3,342


Advertise | Privacy | Mobile
Web04 | 2.8.141220.1 | Last Updated 22 Mar 2013
Copyright © CodeProject, 1999-2014
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