[dba-SQLServer] Control of SortOrder?

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