[dba-SQLServer] Control of SortOrder?

What a shifty little hack that is ;-)))

To do the same with chars I guess you would need to convert them to
ints...
a = 1
b = 2

Don't think there is a builtin function for this but I assume its what
SQL does in the background anyway.

I still don't understand how the 3rd part can actually work?
To me it evaluates to (5-3) which seems to do nothing, at all...

cheers

Michael M

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
pcs at azizaz.com
Sent: Thursday, 11 October 2007 10:12 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [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
>
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com