Conditional sort order in SQL Server window clauses

Question

So, this is not your average “conditional view” on the question ... I have a rather complicated problem here. :-) I want my stored procedure to offer a conditional sort order for the results. This can usually be done as follows:

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

I would like to make a CASE statement around the actual ASC / DESC , but that will not work. The reason this method works is because when @SortAscending not equal to the given value, the SQL server translates the CASE statement to the NULL constant. So, if @SortAscending is 0, you actually have:

 ORDER BY NULL ASC, SortColumn DESC 

Then the first sort expression just does nothing. This works because in a SELECT regular expression you can use a constant in an ORDER BY .

The problem is that the time that I sort in my saved proc is during a SELECT that contains the window function ROW_NUMBER() . So I want to put the CASE statement in its OVER clause, for example:

 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 starting the stored procedure:

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

Since this is a window function clause, converting a CASE statement to a NULL constant is not valid.

Can anyone think that I can conditionally change the UNION ed SELECT s sort order and assign line numbers to each row resulting from these sorted results? I know that I can resort to building the entire query as a string and execute it as fully dynamic SQL, but I would rather avoid this if possible.

---

UPDATE: It seems that the problem is not caused by the CASE statement as such, but by the fact that I used only constant values ​​in the CASE conditional clause. I started a new question about this curious behavior here .

Answer 1

You can assign line numbers in two directions and select one of the external 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 SE data .

Answer 2

You can use constants if you wrap them in SELECT, for example:

 OVER( ORDER BY (SELECT NULL) ) 

So, in your case, you can:

 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, 

Answer 3

You could

• Add the Ascending and Descending column to the subtotals.
• sort by one of them 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 

Answer 4

If you are going to use these line numbers as part of some conditional logic, perhaps something like this will 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 

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

Answer 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; 

Answer 6

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

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