Union with order bys - 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 2005 Forums

Transact-SQL (2005)

Union with order bys

Author

Topic

mori0043
Starting Member

18 Posts

Posted - 2007-07-25 : 15:44:22

Hello, I want to query a database with this:

SELECT *
FROM dbo.vw_Tabs
WHERE PortalId = NULL
ORDER BY TabOrder, TabName

UNION

SELECT *
FROM dbo.vw_Tabs
WHERE PortalId = 0
ORDER BY TabOrder, TabName

Unfortunately I can't order by inside of the select statements with a union between. Is there any way around this?

Thanks in advance...

cognos79
Posting Yak Master

241 Posts

Posted - 2007-07-25 : 16:01:49

No you cant. you can only use order by at the end. If you use order by at the end it should serve your propose.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-25 : 16:08:04

Use this workaround

SELECT *
FROM dbo.vw_Tabs
WHERE PortalId = NULL OR PortalID = 0
ORDER BY CASE WHEN PortalID IS NULL THEN 0 ELSE 1 END, TabOrder, TabName

E 12°55'05.76"
N 56°04'39.42"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-25 : 16:12:39

If you were using more than one object, I do it like this:

SELECT Column1, Column2
FROM
(
SELECT 1 AS OrderBy, Column1, Column2
FROM Table1
WHERE ...
UNION ALL
SELECT 2, Column1, Column2
FROM Table2
WHERE ...
) t
ORDER BY OrderBy, Column1, Column2

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-07-25 : 16:13:42

quote:
Originally posted by Peso

Use this workaround

SELECT *
FROM dbo.vw_Tabs
WHERE PortalId = IS NULL OR PortalID = 0
ORDER BY CASE WHEN PortalID IS NULL THEN 0 ELSE 1 END, TabOrder, TabName

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-25 : 16:14:24

Thanks!
I guess I have to change my SET ANSI_NULL setting now?

E 12°55'05.76"
N 56°04'39.42"

mori0043
Starting Member

18 Posts

Posted - 2007-07-25 : 17:09:36

Unfotunately tkizer's idea won't work. The sort will be off and this is why I need to use the union. It is essentially two queries that I want to tie together. I will try Peso's workaround and let you know how it goes...
THanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-25 : 17:12:43

Tara's approach will work. Look closer.

E 12°55'05.76"
N 56°04'39.42"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-25 : 17:13:07

How will the sort be off in my solution, which really shouldn't be used anyhow since you are only referencing one object? But regardless, how is it wrong?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-25 : 17:14:32

Or, you can get the results of each query (with the order by) into a table variable and do a SELECT from it at the end.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

mori0043
Starting Member

18 Posts

Posted - 2007-07-25 : 20:39:36

Sorry... I read it too fast. The solution works. Thanks Tara!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources