More than one ORDER BY in UNION

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)

More than one ORDER BY in UNION

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-08-16 : 09:29:40

Ken writes "JUST FYI I did read http://www.sqlteam.com/item.asp?ItemID=239

I am using UNION (2 select statements) in one of my queries . I am using one ORDERBY at the end of the second query.

Select A.amount,a.ID from table1 A
UNION
Select B.Amount,B.ID from Table2 B
Order by amount

COMBINED result of both queries is
--------------------------------------------
-80.6300 101
-78.2600 104
-78.2600 106
78.2600 104
78.2600 106
80.6300 101

--------------------------------------------

All negative amounts are returned by 1st query and all positive amounts are returned by 2nd query.

I want the result as below
--------------------------------------------
-80.6300 101
-78.2600 104
-78.2600 106
80.6300 101
78.2600 104
78.2600 106

--------------------------------------------
In other words 1st query should return mount in ASC order of AMOUNT and second query should return results in DESC order of AMOUNT

Any ideas/ help how this can be accomplished"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-16 : 09:39:42

[code]Select * from
(
(Select top 100 percent A.amount,a.ID from table1 A order by A.Amount )
union
(select top 100 percent B.Amount,B.ID from Table2 B order by B.Amount Desc)
) as x[/code]

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-16 : 10:00:20

Or this one

SELECT		Amount,
		ID
FROM		table1
UNION
SELECT		Amount,
		ID
FROM		table2
ORDER BY	CASE WHEN Amount < 0 THEN 0 ELSE 1 END,
		ABS(Amount) DESC,
		ID

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources