Link to home
Start Free TrialLog in
Avatar of quigleyryan
quigleyryan

asked on

SQL Select doesn't work with case statement


select 'any' as field1
union
SELECT distinct [soemthing]  as field1 FROM table1
order by isnumeric(field1), case when isnumeric(field1) = 1 then cast(field1 as decimal) else null end, field1

receive error: ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

I also tried

select 'any' as field1
union
SELECT [soemthing]  as field1 FROM table1
group by field1
order by isnumeric(field1), case when isnumeric(field1) = 1 then cast(field1 as decimal) else null end, field1

which gives me an error that field1 is an invalid column... please help.
thanks
Avatar of Muhammad Ahmad Imran
Muhammad Ahmad Imran
Flag of United Kingdom of Great Britain and Northern Ireland image

select 'any' as field1 from tablename
union
SELECT distinct [soemthing]  as field1 FROM table1
order by isnumeric(field1), case when isnumeric(field1) = 1 then cast(field1 as decimal) else null end, field1
Avatar of quigleyryan
quigleyryan

ASKER

No that doesn't work.. but thanks
OK, do you want to order the entire result set or just the second query so that "any" is always on top?  And are you sure you want to use UNION instead of UNION ALL?
union means to join to select statements

look at the examples


select ename
union
select dname from dept;

*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected

select ename from emp
union
select dname from dept

ENAME
--------------
ACCOUNTING
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
OPERATIONS
RESEARCH
SALES
SCOTT
SMITH
TURNER
WARD

18 rows selected.

Open in new window

i want any at the top... and the second query has alpha numerics that need to be ordered.
this query works when i don't have the case statement in the order by :-(
leoahmad: please notice the '' around Any...
Avatar of Scott Pletcher
select field1
from (
    select 'any' as field1
    union
    SELECT [soemthing]  as field1 FROM table1
    group by field1
) as derived
order by isnumeric(field1), case when isnumeric(field1) = 1 then cast(field1 as decimal) else null end, field1
Scott - that kinda worked.... if i don't use the field1 and only use the column name - why wouldn't the alias work on group by?
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks Scott