Order By in Union - 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

Transact-SQL (2000)

Order By in Union

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-12-23 : 08:19:32

Ullas writes "When i use order by in the union statment for a field which is not in the select, it is not allowing me to do so. But i want to do that in my procedure. Can anybody clarify my doubt?

Thanks in advance."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-12-23 : 08:19:40

You can't. You need to include any column(s) you want to order by in the query.

SamC
White Water Yakist

3467 Posts

Posted - 2003-12-23 : 12:43:15

What of

SELECT Names
FROM MyTable
ORDER BY Age

Sounds like his problem is the UNION is messing things up

SELECT Names
FROM (
  SELECT Names, Age FROM Table1
  UNION ALL
  SELECT Names, Age FROM Table2
)
ORDER BY AGE

Should work.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-12-23 : 13:01:30

That's OK with a union all but with a union will give a different result set (which is why you can't do it). The result set you have could have the same name at different places in the resultset with no indication why - doesn't make sense really.

the union would be the equivalent of
SELECT distinct Names
FROM (
SELECT Names, Age FROM Table1
UNION ALL
SELECT Names, Age FROM Table2
)
ORDER BY AGE

Which will still fail due to not knowing which age to order by.
Would need a group by and an aggregate in the order by.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-23 : 13:25:39

Good explaination, Nigel. Another way of looking at it would be if you used GROUP BY:

SELECT Name
FROM tbl
GROUP BY Name
ORDER BY Age

That also doesn't make sense and is not allowed.... switching to UNION ALL instead of UNION would be equivalent of:

SELECT Name
FROM tbl
GROUP BY Name, Age
ORDER BY Age

Which DOES make sense and is legal -- but the results may look strange.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-23 : 16:20:40

OK...what is my feeble mind missing?

(EDIT: Except for the margaritta damaged cortex...)


USE Northwind
GO


SELECT Col1 
  FROM (
	 SELECT OrderId, Freight AS Col1 FROM Orders
	  UNION
	 SELECT OrderId, UnitPrice As Col1 FROM [Order Details]
	) AS xxx 
ORDER BY OrderId

SELECT Col1 
  FROM (
	 SELECT OrderId, Freight AS Col1 FROM Orders
	  UNION ALL
	 SELECT OrderId, UnitPrice As Col1 FROM [Order Details]
	) AS xxx 
ORDER BY OrderId

Both work...I'm sure I missing a point in here....

(Also, I don't know what practical purpose this would have in the first place...)

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2003-12-23 : 16:30:23

Here's an application - a query that inserts header rows before manager and worker rows and then orders them using nOrder.

select Manager, Organization
FROM (
SELECT 'Manager' as Manager, 'Department' as Department , 1 AS nOrder
UNION
SELECT Manager, Department, 2 AS nOrder from MgrTable
UNION
SELECT 'Workers' , 'Organization', 3
UNION
SELECT Worker, Department 4 As nOrder from WorkerTable
)
Order by nOrder, Manager, Organization

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources