ordering in unions - 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)

ordering in unions

Author

Topic

dev45
Yak Posting Veteran

54 Posts

Posted - 2004-09-23 : 09:29:02

i have a query that goes something like :

select StringA as str
from tableA
inner join(s)....
where.....
union
select case aNumberVariable
when 0 then stringB
when 1 then stringC
else stringD
end as str
from tableA

i don't mind about the order of the 1st select records but i want that the result of the second string (string B,C or D) to be the last one
is there a way ?

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-09-23 : 09:38:23

Return a constant in the query such that you can be sure of the order. I.e.

SELECT 1 AS SortOrder, StringA as str
FROM tableA
INNER JOIN(s)...
WHERE...
UNION
SELECT 2,
CASE aNumberVariable
WHEN 0 THEN stringB
WHEN 1 THEN stringC
ELSE stringD
END as str
FROM tableA
ORDER BY SortOrder

Mark

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2004-09-23 : 09:40:41

HTH..
Select str from
(Select '1' as order_Col, StringA as str from table A ....
Union
Select '2', Select case......) as A order by A.order_col

- Sekar

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2004-09-23 : 09:41:49

Oh. not again .. I am Late.. Damn slow internet connections..

- Sekar

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-09-23 : 09:54:30

I know your pain Sekar, I'm usually on the receiving end!

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources