order by question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

order by question

Author

Topic

eurob
Posting Yak Master

100 Posts

Posted - 2007-04-06 : 08:28:25

[code]
select
auditid * 2 [a],auditdate from audits
order by
case when @var=1 then [a] end
[/code]

When I use a case, sql server returns err.msg:
"Invalid column name 'a'."

When I don't use a case, it accepts the alias, e.g.:
[code]
.. order by [a]
[/code]

So then I could use:
[code]
.. order by auditid * 2
[/code]

which I find unhandy...
Is it not possible to use column alias for sort when using a case in a select ?

robert

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-06 : 08:31:19

"Is it not possible to use column alias for sort when using a case in a select ?"
Yes. You can't do that.

You can use a derived table to do it


select *
from
(
select 
   auditid * 2 [a],auditdate from audits
) a
order by 
   case when @var=1 then [a] end

eurob
Posting Yak Master

100 Posts

Posted - 2007-04-06 : 08:34:11

Thanks, that worked!

robert

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-07 : 02:53:18

or use the expression itself

select
auditid * 2 [a],auditdate from audits
order by
case when @var=1 then auditid * 2 end

Also use AS before alias to make it clear that it is alias name

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-07 : 03:00:10

Also note that it is best practise to follow derived table approach as alias names cant be directly used in WHERE and GROUP BY clauses

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources