union +order by in sql 2005

Question

3.40/5 (2 votes)

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 19:41pm

josh-jw

Add a Solution

Comments

Prakash Thirumoorthy 22-Mar-13 1:45am

have u used temporary table?

josh-jw 22-Mar-13 1:51am

no.

2 solutions

Solution 2

Hi
Try This....

SQL

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

Posted 21-Mar-13 20:39pm

gvprabu

Updated 21-Mar-13 21:00pm

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 . :)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Prakash Thirumoorthy · Accepted Answer · 2013-03-21T19:56:00

Solution 1

hi,

use the following query

SQL

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

Posted 21-Mar-13 19:56pm

Prakash Thirumoorthy

Updated 21-Mar-13 20:25pm

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 :-)