Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 383
  • Last Modified:

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;
0
bschave2
Asked:
bschave2
  • 3
  • 2
1 Solution
 
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
 
sdstuberCommented:
>>> 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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now