SQL Pagination With Dynamic Order By

[rmturner76]

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?

Thanks for your help.

[Ed Murphy]

rm********@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)

[--CELKO--]

> Is there a way to do this in a single query with 2 different data types? <<

The CASE expression has to have one and only one data type, just like
any other expression in SQL and strongly typed languages.

The trick is to keep both columns and NULL one of the out or make it a
constant, somethign like this:

ROW_NUMBER() OVER (ORDER BY
CASE WHEN @x = 1 THEN decimal_col ELSE CAST(NULL AS DECIMAL(s,p)) END,
CASE WHEN @x = 2 THEN text_col ELSE CAST (NULL AS CHAR(n)) END) AS
sort_order

The casting is a bit redundant, but a nice reminder.