ORDER BY using @variables

Hi
I realise you can't declare a column name as a @.variable:
SELECT name, address FROM table WHERE name = 'smith' ORDER BY @.column
....but other than using dynamic SQL, is there a better way i can ORDER a query using various columns?
thankssomething like this perhaps?order
by case @.flag
when 1 then column1
when 2 then column2
when 3 then column3
else null end|||Ordering logic belongs at the presentation layer, not the database layer.|||WHAT??!!!!

please explain, oh database guru

you're suggesting that we ditch the ORDER BY clause altogether?

something about that idea just doesn't sit too well with me...|||I'm gonna report this thread|||Thanks for that r937, works spot on!

Just have another question, for some reason i get the following error when trying to add the case statement to a UNION query:

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

Now i know that you can define a single ORDER BY clause in a union query and that it should appear after the last SELECT which it is. I've tried manually putting the ORDER BY clause into the query and it works fine, but it just doesnt like the case statement for some reason? Is there a way round it?

cheers|||Just note two conditions on performing UNIONS :-

The number and the order of the columns must be the same in all queries.
The data types must be compatible.|||Is there a way round it?there might be, but i can't really help because i can't see the query from here|||sorry, should have put it in - this is a simlified version of my MSSQL query - it still brings up the same error:

SELECT business_name, address1
FROM VENUE
WHERE (business_name LIKE '%' + @.v_name + '%')
UNION
SELECT business_name, address1
FROM AHOTELS
WHERE business_name Like '%' + @.v_name + '%'
ORDER BY CASE @.sortBy
WHEN 1 THEN business_name
WHEN 2 THEN postcode
WHEN 3 THEN town
ELSE NULL END|||WHAT??!!!!

please explain, oh database guru
Ordering is normally a presentation issue. Databases should not be concerned with the manner in which data is presented.
you're suggesting that we ditch the ORDER BY clause altogether?
Of course not. Would you suggest that we ditch cursors just because they are often misused?|||it still brings up the same error:which is... ?|||Ordering is normally a presentation issue. Databases should not be concerned with the manner in which data is presented.next time i see you post a SELECT statement with an ORDER BY clause, you are gonna get hit with a big can of whoopass from me, then|||This is the error:

ORDER BY items must appear in the select list if the statement contains a UNION operator.|||well, that's pretty clear, isn't it ;)

SELECT business_name, address1, postcode, town
FROM VENUE
WHERE (business_name LIKE '%' + @.v_name + '%')
UNION
SELECT business_name, address1, postcode, town
FROM AHOTELS
WHERE business_name Like '%' + @.v_name + '%'
ORDER BY CASE @.sortBy
WHEN 1 THEN business_name
WHEN 2 THEN postcode
WHEN 3 THEN town
ELSE NULL END|||but I'm sure they don't want it in the result set|||but I'm sure they don't want it in the result set
so what's your suggestion in that case?

are you going to join the blindman parade and suggest that ordering should be done in the front end application?

if you don't include postcode and town in the result set, and then pass that result set to the front end app, then how are you gonna sort by postcode or town??

come on, brett, you're a smart guy, i'd like to see your solution|||hi, sorry, the code i pasted was a shortened version and the original did include postcode and town (apologies, will not shorten code in the future!)

I'm using the column names in the first SELECT, is this right?

Also, as i've mentioned, when i use a single ORDER BY, eg:

...
ORDER BY business_name

this works, but if i try:

...
ORDER BY CASE @.sortBy
WHEN 1 THEN business_name
ELSE NULL END

This surely should do the same thing (assuming @.sortBy is 1) but does not work and comes up with that error!! (btw i'm using MS SQL server managemnt studio, and the error comes up when i try to execute(save) the stored proc!)|||Nobody open a can of whoopass on me please :)


ORDER BY CASE
WHEN @.sortBy = 1 THEN business_name
ELSE NULL END


EDIT - misread the SQL - sorry no real change made|||When heating a can of whoopass, should I mix it with 1 can of water or 1 can of milk?

Also, will one can of whoopas be enough to feed everybody in my parade? Or will you open up a family-size can of whoopass for me?

You won't find ORDER BY in my select statements unless specifically requested by the developers and then it would be against my advice. But anyway, I consider the ordering of data in result sets to be at most a minor transgression.|||Here is the essence of the problem, then?

create table test1
(col1 int, col2 varchar(30))

create table test2
(col3 int, col4 varchar(30))

insert into test1 values (1, 'hello')
insert into test1 values (3, 'aloha')
insert into test2 values (2, 'bye')
insert into test2 values (4, 'auf wiedersehen')

select col1, col2 from test1
union
select col3, col4 from test2
order by col1

select col1, col2 from test1
union
select col3, col4 from test2
order by case when 1 = 1 then col1
when 1 = 2 then col2 end -- OK. Dummy cases, but it generates an error.

drop table test1
drop table test2

Now, that is a conundrum. This will take some thought...|||just found a blog here (http://www.sqlblogs.com/top/ng/group~22/~117449~__order-by-and-UNION/index.aspx)which refers to a ANSI SQL-92 standard (??) that you can't use Expressions in an ORDER clause when used with a UNION!! Does this sound right??

If so, are there any other methods which i can try which do the same as a UNION?

ta|||yup MCrowley, that is the problem in essence!!|||This is ugly, but it gets the job done. Performance-wise it is probably the same thing:

select * from
(select col1, col2 from test1
union
select col3 , col4 from test2) a
order by case when 1 = 1 then col1
when 1 = 2 then col2 end

EDIT: Removed confusing extra characters.|||New problem:

select * from
(select col1, col2 from test1
union
select col3 , col4 from test2) a
order by case when 1 = 2 then col1
when 1 = 1 then col2 end

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'hello' to a column of data type int.

Are all of your datatypes in the order by the same, or at least similar datatypes?|||yup, they are all varchar apart from @.sortBy which is an int. Like i said before, the UNION works fine on its own and with a single ORDER BY clause, its only when the case statement gets thrown into it that is has an error!|||Then you should be all set with the solution in post 23. the problem only comes up when you have an int and a varchar as a result of the case statement.|||Ok thanks MCrowley, will try it!