Posted by Ed Murphy on 04/04/07 15:52

rmturner76@gmail.com wrote:

> I have a need to perform pagination while using dynamic sorting. As
> an exmaple -
>
> SELECT TOP(10) * FROM (
> SELECT
> TextColumn,
> DecimalColumn,
> ROW_NUMER() OVER (
> ORDER BY
> CASE @x
> WHEN 1 THEN TextColumn
> WHEN 2 THEN DecimalColumn
> END
> DESC
> ) AS SortOrder
> FROM Table1
> ) AS Results WHERE SortOrder > ( 10 ) ORDER BY SortOrder
>
> This is obviously just some sample but an error is given because the
> data type of the 2 columns used in the order by are different. It
> works if I cast DecimalColumn to match the textcolumn but then the
> sorting is wrong. Is there a way to do this in a single query with 2
> different data types?

Try this:

WHEN 2 THEN
right(' '+cast(DecimalColumn as varchar(20)), 20)

[Back to original message]