union keyword - 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

SQL Server 2000 Forums

SQL Server Development (2000)

union keyword

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-07-15 : 10:33:54

Henry Joshua writes "Dear Friends,
I am using two tables R1 and R2, which has 2 fields rollno and name. I want to select all the records from both the tables and want to sort the resultant by rollno. I dont want an individual sort. I used this statement, but it doesn't works

Select * from (
Select * from R1 union all
Select * from R2) order by rollno

Can you help me.

Thanx

Joshua"

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2004-07-15 : 10:33:54

What do you mean it doesn't work? Does it generate an error? If so, what error? Does it give incorrect results?

Try:

Select * from (
Select * from R1
union all
Select * from R2) as d
order by rollno

(notice the "as d" which is needed for derived tables)

Kristen
Test

22859 Posts

Posted - 2004-07-15 : 10:54:54

Wouldn't


Select * from R1 
union all
Select * from R2
order by rollno

work too?

Kristen

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2004-07-15 : 13:01:42

Where's the sheepish grin emoticon? Yeah, it should.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-15 : 13:04:19

SELECT *?

You guys are killing me....you actually believe that there are only 2 columns in those tables?

Post the DDL

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-07-15 : 13:07:35

Its just an example Brett. Honest. No, honestly, I'm absolutely sure ...

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-15 : 13:09:06

Why doesn't this work?


USE Northwind
GO

   SELECT * FROM Orders
UNION ALL
   SELECT * FROM [Order Details]

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-07-15 : 13:21:34

Because its NOT a suitable example ... :)

The columns are different right?
Is this some sort of trick question?
I bet I fall for it ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources