ordering challenge - 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

General SQL Server Forums

New to SQL Server Programming

ordering challenge

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-05-09 : 15:53:54

Hey, everyone,

I need some help. I am converting a piece of select query from oracle. Next sample data will demo the issue I have.

The requirement calls for the to have the to select has asc order but keep the ***EOF*** as the last line.

Because of the union, I could not get both in one select. The right output should be:

011|John|Doe
012|Jim|Dunn
013|Kathy|Smith
***EOF***

if object_ID('tempdb..#test') is not null drop table #test;

create table #test
(id varchar(10),
fname varchar(10),
lname varchar(10)
)

insert into #test
select '011', 'John', 'Doe'
union
select '012', 'Jim', 'Dunn'
union
select '013', 'Kathy', 'Smith'

select id +'|'+ fname + '|' + lname from #test
union
select '***EOF***';
order by 1 desc

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-09 : 16:11:01

Can you make the UNION'ed query into a subquery?


SELECT * FROM 
(
select id +'|'+ fname + '|' + lname AS col from #test
union 
select '***EOF***'
) s
order by CASE WHEN col = '***EOF***' THEN 1 ELSE 0 END ASC , col ASC

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-05-09 : 16:37:07

I am thinking along the same subquery way, but haven't come up with the order by case part.

Brilliant! Thanks!

quote:
Originally posted by James K

Can you make the UNION'ed query into a subquery?

SELECT * FROM
(
select id +'|'+ fname + '|' + lname AS col from #test
union
select '***EOF***'
) s
order by CASE WHEN col = '***EOF***' THEN 1 ELSE 0 END ASC , col ASC

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-09 : 17:26:52

You are very welcome - glad to help.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-05-09 : 21:40:21

like this?


select 1, id +'|'+ fname + '|' + lname from #test
union	
select 2, '***EOF***'
order by 1

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-05-10 : 08:43:14

Now that was something I came up with, then I tried to take out the first column by this next, but it didn't work. :(

select col from
(select 1, id +'|'+ fname + '|' + lname col from #test
union
select 2, '***EOF***'
order by 1) s

Msg 1033, Level 15, State 1, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Now I have the solution implemented, thanks to James, I am curious how exactly the "order by case..." part works, and why Oracle carry out the same task without that twist. Can someone enlight me?

quote:
Originally posted by waterduck

like this?

select 1, id +'|'+ fname + '|' + lname from #test
union
select 2, '***EOF***'
order by 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources