Union all with order by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Union all with order by

Author

Topic

Bjcascone
Starting Member

37 Posts

Posted - 2010-04-29 : 10:56:09

I am trying to select 2 tables into a new table using a union. i need to keep the order of the Identity field of both tables. So far it looks something like this:

Select
Field1
,Field2
,Field3
Into DB..Newtable
From
(
Select
Field1
,Field2
,Field3
From DB..OldTable1
Order by field3

Union All

Select
Field1
,Field2
,Field3
From DB..OldTable2
Order by field3
) a

can any give me any ideas on how to perform this?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-29 : 11:08:20

[code]Select
Field1
,Field2
,Field3
Into DB..Newtable
From ( SELECT
Field1
,Field2
,Field3
FROM (
Select
Field1
,Field2
,Field3
From DB..OldTable1

Union All

Select
Field1
,Field2
,Field3
From DB..OldTable2
) a
) b
Order by field3[/code]

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-29 : 11:08:25

Which version of SQL Server are you using?

Bjcascone
Starting Member

37 Posts

Posted - 2010-04-29 : 11:10:31

SQL 2008

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-29 : 11:12:57

Try this:


Select Field1
	  ,Field2
	  ,Field3
Into DB..Newtable
From (Select Field1
            ,Field2 
		    ,Field3
		    ,recID = ROW_NUMBER() OVER(ORDER BY field3)
		    , k=1
	  From DB..OldTable1      

       Union
 
	Select Field1
		 ,Field2 
		,Field3
		,recID = ROW_NUMBER() OVER(ORDER BY field3 ASC)
		, k =2
	From DB..OldTable2	
	) a
	order by K ASC, recID ASC

Bjcascone
Starting Member

37 Posts

Posted - 2010-04-29 : 11:25:31

DBA in the making- thank you for your help I believe your solution worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources