SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 ordering challenge
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hommer
Aged Yak Warrior

794 Posts

Posted - 05/09/2013 :  15:53:54  Show Profile  Reply with Quote
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

Edited by - Hommer on 05/09/2013 16:05:04

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 05/09/2013 :  16:11:01  Show Profile  Reply with Quote
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
Go to Top of Page

Hommer
Aged Yak Warrior

794 Posts

Posted - 05/09/2013 :  16:37:07  Show Profile  Reply with Quote
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


Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 05/09/2013 :  17:26:52  Show Profile  Reply with Quote
You are very welcome - glad to help.
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
965 Posts

Posted - 05/09/2013 :  21:40:21  Show Profile  Reply with Quote
like this?

select 1, id +'|'+ fname + '|' + lname from #test
union	
select 2, '***EOF***'
order by 1
Go to Top of Page

Hommer
Aged Yak Warrior

794 Posts

Posted - 05/10/2013 :  08:43:14  Show Profile  Reply with Quote
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



Edited by - Hommer on 05/10/2013 08:43:58
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000