added a comment - - edited
There are four major issues with this:
1: SQLServerPlatform.php modifies the query to prepend 'SELECT ROW_NUMBER() OVER ($over)', which is inserted before the DISTINCT keyword.
2: The order needs to be placed inside the OVER($over) block. At this point, the regex is using the exact column name rather than the alias, so the outer query cannot ORDER.
3: The DISTINCT queries select only the ID columns - as OVER() required the sort column to be available in the outer query, IDs alone will not work.
4: SQL Server cannot DISTINCT on TEXT columns. 2005,2008 and 2012 recommend using VARCHAR(MAX) instead, which does support it. That doesn't help us with 2003. We work around that with a custom TEXT type that casts as varchar.
Incidentally, 2012 supports LIMIT, which gets rid of this issue altogether.
Edit: Added #3