Using CASE in ORDER BY clause - 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 2000 Forums

Transact-SQL (2000)

Using CASE in ORDER BY clause

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-01-02 : 08:33:22

Emery writes "I am analyzing a variable in a case statement inside an order by clause. One of the columns that can be chosen is a 'derived' column inside the select clause. When using the case statement I am told the 'derived' column is not a valid column name. When I don't use the case statement and just sort on the 'derived' column everything works fine. Here is my code, can anyone point out the problem:

CREATE PROCEDURE dbo.fund_by_daterange(@sDate varchar(10),@eDate varchar(10),@Sort varchar(10))
AS
BEGIN
SELECT
FundName,
ISNULL(SUM(DonationAmount), 0) AS FundTotal
FROM dbo.vFiscalYearDonations
RIGHT OUTER JOIN
dbo.FundType ON dbo.vFiscalYearDonations.FundUid = dbo.FundType.FundPrefix
WHERE (CONVERT(varchar,TransactionTimeStamp,101)
BETWEEN CONVERT(smalldatetime,@sDate) AND CONVERT(smalldatetime,@eDate))
AND (dbo.FundType.Active = 'Y')
GROUP BY
dbo.FundType.FundName,
dbo.FundType.FundPrefix
ORDER BY
CASE @Sort
WHEN 'FundName' THEN FundName
ELSE FundTotal DESC
END

GO"

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2004-01-02 : 09:59:09

<code>
ORDER BY
CASE @Sort
WHEN 'FundName' THEN FundName else '' end,
case when @sort <> 'FundName' then ISNULL(SUM(DonationAmount), 0) end desc
</code>

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-01-02 : 11:22:32

or, if you need to use a derived column's alias further in a query, it is often easier to say:

SELECT *
FROM
(some SQL) A
ORDER BY
A.AnyColumnAlias

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources