Conditional sort order in SQL Server windowed func

问题:

So, this is not your average 'conditional sort by' question... I have a rather tricky problem here. :-) I want to allow my stored procedure to offer a conditional sort order for the results. Normally this can be done in the following manner:

SELECT *
INTO #ResultsBeforeSubset
FROM
    MyTable
ORDER BY
    CASE WHEN @SortAscending=1 THEN 'SortColumn' END ASC,
    CASE WHEN @SortAscending=0 THEN 'SortColumn' END DESC

I'd like to do a CASE statement around the actual ASC/DESC, but that doesn't work. The reason the above method works is because, when @SortAscending isn't equal to the given value, SQL server translates the CASE statement into the constant NULL. So, if @SortAscending is 0, you effectively have:

ORDER BY
    NULL ASC,
    SortColumn DESC

The first sort expression, then, just does nothing. This works because in a regular SELECT statement you can use constant in an ORDER BY clause.

Trouble is, the time that I'm sorting in my stored proc is during a SELECT statement which contains a windowed function ROW_NUMBER(). I therefore want to put the CASE statement inside its OVER clause, like so:

SELECT *
INTO #ResultsBeforeSubset
FROM (
    SELECT
        ROW_NUMBER() OVER (
            ORDER BY
                CASE WHEN @SortAscending=1 THEN rowValues.[SortColumn] END ASC,
                CASE WHEN @SortAscending=0 THEN rowValues.[SortColumn] END DESC
        ) AS RowNumber,
        *
    FROM (
        -- UNIONed SELECTs returning rows go here...
    ) rowValues
) rowValuesWithRowNum

Unfortunately, this causes the following error when you run the stored procedure:

Windowed functions do not support constants as ORDER BY clause expressions.

Because this is the clause of a windowed function, the conversion of the CASE statement to the constant NULL is invalid.

Can anyone think of a way that I can conditionally vary the sort order of UNIONed SELECTs, and assign row numbers to each row resulting from these sorted results? I know I could resort to constructing the entire query as a string and execute that as fully dynamic SQL, but I'd rather avoid that if possible.

---

UPDATE: Looks like the problem wasn't caused by the CASE statement per se, but by the fact that I was using only constant values in the CASE statement's conditional clause. I've started up a new question on this curious behaviour here.

回答1:

You could assign row numbers in two directions, and pick one in an outer order by:

select  *
from    (
        select  row_number() over (order by SortColumn) rn1
        ,       row_number() over (order by SortColumn) rn2
        ,       *
        from    @t
        ) as SubQueryAlias
order by
        case when @asc=1 then rn1 end
,       case when @asc=0 then rn2 end desc

Working example at SE Data.

回答2:

You could use constants if you wrap them in a SELECT, such as:

OVER( ORDER BY (SELECT NULL) )

So in your case you should be able to do:

SELECT
    ROW_NUMBER() OVER (
        ORDER BY
            (SELECT CASE WHEN @SortAscending=1 THEN rowValues.[SortColumn] END) ASC,
            (SELECT CASE WHEN @SortAscending=0 THEN rowValues.[SortColumn] END) DESC
    ) AS RowNumber, 

回答3:

You could

• add both an Ascending and Descending column to your intermediate results
• sort on one of those at the end.

SQL Statement

SELECT  *
INTO    #ResultsBeforeSubset
FROM    (
          SELECT  ROW_NUMBER() OVER (ORDER BY rowValues.[SortColumn] ASC) AS AscSortColumn
                  , ROW_NUMBER() OVER (ORDER BY rowValues.[SortColumn] DESC) AS DescSortColumn
                  , *
          FROM    (-- UNIONed SELECTs returning rows go here...
                  ) rowValues
        ) rowValuesWithRowNum
ORDER BY
        CASE  WHEN @SortAscending = 1 
              THEN rowValues.[AscSortColumn] 
              ELSE rowValues.[DescSortColumn] 
        END 

回答4:

If you're going to use these row numbers as part of some other conditional logic, maybe something like this would work:

CASE WHEN @SortAscending=1 THEN COUNT(*) OVER() + 1 ELSE 0 END +
(CASE WHEN @SortAscending=1 THEN -1 ELSE 1 END *
    ROW_NUMBER() OVER (ORDER BY SortColumn DESC)) as RowNumber

This can even be extended so that if you're using PARTITION clauses, it continues to work so long as both OVER() expressions use the same PARTITION clauses.

回答5:

DECLARE @sign int = -1;
IF @SortAscending = 0 SET @sign = -1;

SELECT ROW_NUMBER() OVER (ORDER BY RowNumber) AS RN,
    *
INTO #ResultsBeforeSubset
FROM (
    SELECT
        @sign * ROW_NUMBER() OVER (ORDER BY rowValues.[SortColumn]) AS RowNumber,
        *
    FROM MyTable
) rowValuesWithRowNum
ORDER BY RN

--DECLARE @sign int = 1;
--IF @SortAscending = 0 SET @sign = -1;
--
--SELECT *
--INTO #ResultsBeforeSubset
--FROM (
--    SELECT
--        @sign * ROW_NUMBER() OVER (ORDER BY rowValues.[SortColumn] AS RowNumber,
--        *
--    FROM MyTable
--) rowValuesWithRowNum
--ORDER BY RowNumber;

回答6:

You can use a windowing function in the order by, not just in the select. The windowing functions row_number, rank, dense_rank, all return a number (bigint) which you can multiply by a negative value to get the reverse...

SELECT *
INTO #ResultsBeforeSubset
FROM MyTable
ORDER BY
  (rank() over (order by SortColumn)) * case when @asc=1 then 1 else -1 end