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
quigleyryanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Muhammad Ahmad ImranDatabase DeveloperCommented:
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
quigleyryanAuthor Commented:
No that doesn't work.. but thanks
tim_csCommented:
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?
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Muhammad Ahmad ImranDatabase DeveloperCommented:
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

quigleyryanAuthor Commented:
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 :-(
quigleyryanAuthor Commented:
leoahmad: please notice the '' around Any...
Scott PletcherSenior DBACommented:
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
quigleyryanAuthor Commented:
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?
Scott PletcherSenior DBACommented:
Meant to change to this:
SELECT DISTINCT [something]  as field1 FROM table1
[DISTINCT is often more efficient than GROUP BY.]


>> if i don't use the field1 and only use the column name - why wouldn't the alias work on group by? <<

SQL does not assign the alias name in such a way that it can be used prior to an ORDER BY.  That is, assigning the value to the alias name essentially does not occur until the result set is generated.

Likewise, you can't use the alias in a WHERE or later in the SELECT.  For example, this also does NOT work:

SELECT a + 1 AS b, b + 1 AS c  FROM ...

Same problem, you can't reference alias "b" .

Note, though, that by using an inner query, like above, the inner query is essentially resolved before the outer query, so the alias names are fully available to the outer query, and so can be used like any column name.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
quigleyryanAuthor Commented:
thanks Scott
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.