I stumbled across the task of sorting numbers present in a comma separated string, based on their numerical value in SQL.
Curious as always I thought it would be possible to solve such a problem using XML and XQuery in SQL and indeed it was.

Given a numerical string, then in SQL Server 2005 and up, you can do something like this:
DECLARE @X XML = ''

DECLARE @STR VARCHAR(255) = CAST(@X.query('for $i in (11, 9, 10)
                  order by $i
                  return fn:concat(xs:string($i), ",")') AS VARCHAR(255))
                  
SELECT LEFT(@STR, LEN(@STR) -1)  
(Remember, in SQL 2005, you need to put assignment on another line, this is 2008 syntax)

The output from this query will be the value: '9, 10, 11'

Now, it might not be special or terrible useful as such – but the point of the post is also much more that untraditional methods for solving problems exists, and one can think outside the box and solve many problems by doing so.

A problem with this is that if you want the input created dynamically - you either need to build the entire segment within a string and use EXEC, or perhaps you can use the sql:column and sql:variable to help you along.

Occasionally I have the problem that I need to run as query with a form of dynamic sorting - meaning sometimes ascending or descending and sometimes based on an ordernumber or a date, for example.

Now a CASE statement in the order by is often very effective for handeling such issues, but I've not found it possible to combine it with the Ascending and Descending clasuses (yet, could be that I'm just doing it wrong). 

For example suppose we have a table which contains a "Date" field of the type DateTime, and we need to be able to sort this both descending and ascending based on some input parameter. Instead of making two queries, one might think that something like this would work

DECLARE @ASC BIT
SET @ASC = 1
SELECT *
FROM DateTimeOrderTest
ORDER BY DATE
    CASE @ASC
        WHEN 1 THEN ASC
        ELSE DESC
    END

But well - it doesn't: "Incorrect syntax near the keyword 'CASE'."

Or one might think:

DECLARE @ASC BIT
SET @ASC = 1
SELECT *
FROM DateTimeOrderTest
ORDER BY
    CASE @ASC
        WHEN 1 THEN Date ASC
        ELSE Date DESC
    END

Which gives a nice little: Incorrect syntax near the keyword 'ASC'

A little fix to get around this would be to not utilize case, but instead make the Ascending and Descending a factor so when Ascending you have a factor of 1 and Descending you have a factor of -1. Then simply convert/cast the Date to an integer (which should not be a terrible expensive procedure, as the datetime is represented as a number anyway, I wonder if it is possible to somehow use the internal representation for my purpose, or if the database does that when casting as integer....oh, well), and multiply the factor to the order by.

This mean the following two queries give the same result:

SELECT  *
FROM DateTimeOrderTest
ORDER BY CAST(Date AS INT) * -1

SELECT *
FROM DateTimeOrderTest
ORDER BY Date DESC

This technique of multiplying either 1 or -1, can be used in many queries, and of course is not limited to datetimes.

Anyways, I've found it quite useful myself. 

I ran into a SQL oddity today in MS SQL 2005 (and likely all prior versions) - or at least I think it's an oddity.
It revolves around case statements and column aliases, and for some reason you can't use column alias in a case statement. At least neither in the select nor order by clause.

For example:

SELECT myColumn AS C
FROM myTable
ORDER BY C 

This works perfetly well.

Now spice it up with a case (for what ever reason)

SELECT myCoulmn AS C
FROM myTable
ORDER BY
 CASE myCondition
 WHEN myValue THEN C
END 

This for some reason fails because it can't find column C......

I've yet to find the actual reason for why this happens, but that looks odd to me, and it is quite limiting at times.