How do I word case statement in order by clause with a union?

I need to add a 'all' company type in my result for a drop down list, however the order in which  the "all" company is showing is at position 8 or 9 and I want 'all' to show up at position 0 in the result order.

this is what I have s far:

SELECT COM_ID, COM_SHORT_NAME FROM TBLCOMPANY
UNION
SELECT 0 AS COM_ID, 'ALL' AS COM_SHORT_NAME FROM DUAL
ORDER BY CASE 2 WHEN 'ALL' THEN 1 END ASC;
bschave2Asked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
>>> I WANT TO SORT BY COM_SHORT_NAME

does that mean com_id might be negative or 0 for some rows in TBLCOMPANY?  if not...

order by 1,2   -- no need for case


if it is or might be so,  or if you just want to use case....


select * from (
SELECT COM_ID, COM_SHORT_NAME FROM TBLCOMPANY
UNION
SELECT 0 AS COM_ID, 'ALL' AS COM_SHORT_NAME FROM DUAL
)
order by case when com_short_name = 'ALL' then 1 else 2 end, com_short_name
0
 
sdstuberCommented:
assuming you have non-zero com_id's in your table and you want 0 to be first...


order by 1
0
 
bschave2Author Commented:
I WANT TO SORT BY COM_SHORT_NAME
0
 
bschave2Author Commented:
and the com id for all is 0
0
 
bschave2Author Commented:
I have a case statement in the order by clause.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.