[dba-SQLServer] Control of SortOrder?

Michael,

Since the column to sort on is Integer I was suggested to 
use this workaround... which works:

ORDER BY
 CASE
	WHEN @p7_ReqPosID = 0
	THEN T2.HighestQualificationEnum * -1 
	ELSE T2.HighestQualificationEnum 
 END DESC,

..notice placement of the DESC

But what to do if the column was a char field?

The last CASE statement below has been tested to work ok 
too..

Regards
Borge

---- Original message ----
>Date: Thu, 11 Oct 2007 18:25:23 +1000
>From: "Michael Maddison" <michael at ddisolutions.com.au>  
>Subject: Re: [dba-SQLServer] Control of SortOrder?  
>To: <dba-sqlserver at databaseadvisors.com>
>
>Borge,
>
>I believe you must Order By a field.  BTW I'm testing in 
SQL2K
>
>So,
>T3.Ranking, is fine
>
>I don't believe you can change ASC to DESC this way.
>'(CASE WHEN @p7_ReqPosID = 0 THEN ASC ELSE DESC END)'
>It wont compile for me because of the reserved words ASC & 
DESC
>
>The last one is interesting, but is it doing what you think 
it is?
>The case statement seems to return a #, I assumed it will 
then sort by
>the column ordinal returned???
>I couldn't actually get it to work though, it would compile 
but had no
>effect on sort order.
>
>When I have complicated sort orders I would create a 
computed column to
>then sort on.
>Append your 3 columns together in a way that makes sense 
and sort on
>that?
>
>cheers
>
>
>Michael M
>
>Hello,
>
>SQL2005
>
>Based on the value of an input parameter I want to control 
the SortOrder
>on a particular column.
>
>Can it be done, and if so how?
>
>I've been trying with CASE statements .. no luck, I'm stuck!
>
>I've tried
>
>ORDER BY
>T2.HighestQualificationEnum (CASE WHEN @p7_ReqPosID = 0 
THEN ASC ELSE
>DESC END), T3.Ranking,
>@p2_intNumberOfDays-
>	CASE
>	 WHEN datediff(d, CONVERT(DATETIME,
>@p1_ReqStartDate , 102),T1.StartDate)>=@p2_intNumberOfDays
>	 THEN @p2_intNumberOfDays
>	 ELSE datediff(d, CONVERT(DATETIME,
>@p1_ReqStartDate , 102),T1.StartDate)
>	END
>
>(The last sortorder item that also includes a CASE statement
>works!)
>
>regards
>borge
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>