Sep
24
Written by:
Steve Gray
9/24/2009 7:49 AM
You heard it here first, folks.
I searched the web for this solution, but could not find anything that worked. After a bit of hacking, I came up with a way to put variables in an ORDER BY clause, and even have it work for GROUP BY queries. Read on.
This statement (written against AdventureWorks) will generate the following error:
declare @col varchar(100)
set @col = 'CustomerID'
select *
from sales.salesorderHeader
order by @col
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
Fix it like this:
declare @col varchar(100)
set @col = 'CustomerID'
select *
from sales.salesorderHeader
order by case when @col = 'CustomerID' then customerID
else salesOrderID end
Now here is the beauty - I can make it work against aggrigated columns, too:
declare @col varchar(100)
set @col = 'unitprice'
select salesorderid,
sum(unitprice) as unitprice,
sum(linetotal) as linetotal
from sales.salesorderdetail
group by salesorderid
order by case when @col = 'unitprice' then sum(unitprice)
else salesOrderID end
2 comment(s) so far...
Re: The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position
Thanks for such a beautifully composed, informative article.I think your work to this is really great .I really appreciate your work to this site.So thanks for it.I hope you can continue this type of hard work to this site in future also..Because this blog is really very informative and it helps me lot.
By Jack on
6/18/2010 11:02 AM
|
Re: The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position
Nice solution, but it only works if both possible sort expressions have the same datatype. E.g. "order by case when @col = 'CustomerID' then customerID else SomeCharacterField end" doesn't work because of the CASE construct that expects the same data type for it output options.
By Geert-Jan Thomas on
6/21/2010 2:59 AM
|
As always, I welcome your comments!