ORDER BY problem

[ColinM]

Got a query like the following:

use pubs
select state from publishers
union all
select state from authors
order by (case authors.state when 'UT' then 1 else 2 end),authors.state

Which should order states with 'UT' first. It works fine on just the authors table, but when the union is introduced I get an error:

The column prefix 'authors' does not match with a table name or alias name used in the query.

:-(

 

[foxdev]

This is because in a UNION the column names are taken from the first SELECT; this is true even of the ORDER BY clause. Try leaving off the table name. Robert Bradley

http://www.foxdev.com/

Do you have too much money? Visit

http://www.agrainofhope.org

 

[ColinM]

I've tried it without table names or aliases and get this error:

ORDER BY items must appear in the select list if the statement contains a UNION operator.

Even thought "state" appears in both select lists? (or even when just in one)

Any other ideas? I'm stumped (again!)

 

[Guest_imported]

i try run your query and it seems you try to extract 'UT'
from author which doesn't found in publishers (am i correct?). so i change it to the one below:

select ordrid=2,state from publishers
union all
select ordrid=(case when state = 'UT' then 1 else 2 end),state from authors
order by ordrid,state

provided, 'UT' is not found in publishers. I know the 'ordrid' is a bit annoying when you just want 'state' field but i can't seems to get rid of it.

unless someone knows better...