Conditional sort order in SQL Server windowed function clauses

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.

-------------Problems Reply------------

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.

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

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.

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,

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;

Category:sql Views:3 Time:2011-08-26

Related post

  • Save and get arbitrary sort order in SQL Server 2009-12-09

    My client wants to sort products by drag & drop. The drag & drop part is easy with javascript. My problem is how do I save and get the sort order? I'm using .net c# and SQL Server 2008. When I move a product and drop it in a new position I ge

  • Sort Order in SQL Server 2010-08-26

    Can I change the default sort order in a SQL server database so that nulls and zero length strings are displayed last. Its SQL Server 2000 I stress, I want to change the default order for all queries, if possible --------------Solutions-------------

  • SQL Server window functions: can you do a ROW BETWEEN type construct? 2011-06-29

    This is my version of SQL Server: Microsoft SQL Server 2008 (SP2) - 10.0.4064.0 (X64) Feb 25 2011 13:56:11 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2) I want to do something

  • Custom sort-order with SQL-Server and .NET Entity-Framework 2009-12-20

    I have a table I store contacts and their phones. Contact: ContactId (int, PK), FirstName (varchar), LastName (varchar) Phone: PhoneId (int, PK), ContactId(int FK), Number (varchar), SortOrder (tinyint) I want that under each contact, the user should

  • Custom sort order in SQL Server mixed datatypes stored in a varchar 2012-04-23

    I have this declare @testtable table (test nvarchar(max)) insert into @testtable (test) values ('1.2.3') insert into @testtable (test) values ('1.20.3') insert into @testtable (test) values ('1.19.x') insert into @testtable (test) values ('1.x.x') in

  • Are there any built in SQL Server 2005 functions to serialize / deserialize string parameters to a table? 2009-12-04

    The big question is are there any "built in" SQL Server 2005 functions to serialize / deserialize string parameters to a table variables? The rest explains my need further, but may not be necessary. I have a SQL SP I would like to refactor. It curren

  • Oracle equivalent to SQL Server STUFF function? 2010-01-21

    Does Oracle have its own implementation of SQL Server stuff function? Stuff allows you to receive one value from a multi row select. Consider my situation below ID HOUSE_REF PERSON 1 A Dave 2 A John 3 B Bob I would like to write a select statement, b

  • Designing a conditional database relationship in SQL Server 2010-01-21

    I have three basic types of entities: People, Businesses, and Assets. Each Asset can be owned by one and only one Person or Business. Each Person and Business can own from 0 to many Assets. What would be the best practice for storing this type of con

  • Validate a string in a table in SQL Server - CLR function or T-SQL (Question updated) 2010-03-13

    I need to check If a column value (string) in SQL server table starts with a small letter and can only contain '_', '-', numbers and alphabets. I know I can use a SQL server CLR function for that. However, I am trying to implement that validation usi

  • Rearrage column order in sql server 2009-07-20

    How to rearrange column order in sql server ? right now my column is showing like below when i physically right click and take properties: in sql server 2005 colA1 colA2 colA3 colA4 colB1 colB2 colB3 colA5 colA6 Since i know these columns (colA5,colA

  • Moving C# in Process Functions to SQL Server CLR functions 2010-09-30

    What are the limitations, unexpected pitfalls and performance traits of moving from in process C# code to SQL CLR Functions? We currently have several data heavy processes that run very fast using in process C# Asp.net MVC project without using a db

  • SQL Server CLR Function Set timeout 2011-03-22

    I have a SQL Server CLR function that is doing quite a bulky task. I am calling it from another SPROC and I want the SPROC to give the CLR function 10 seconds to return results. If the CLR function hasn't returned in this time thenI want the calling

  • Need to use a variable in an SQL Server string function 2011-06-23

    I am not a great SQL user and am looking to solve what may be a simple problem. While I eventually will need to "loop thru" a list of strings that is the result of a single column query, I first need to solve this problem: I would like to use a varia

  • Resolving permissions when using SQL Server Windows Authentication 2011-08-19

    This is for SQL Server 2005 or later, but I'd be interested to know if SQL Server 2000 works in the same way. Consider the following situation. Two SQL Server Windows Authentication logins whose Login name is a Windows group: MyDomain\Group1 MyDomain

  • Find chars in any order in Sql Server 2012-04-17

    I have a datatable in sql server 2005 containing words in spanish. I have stored each word in a row. Given a char array, I would like to find all the words which contains those characters, no mather in wich order they appear. For example, "avi" shoul

  • SQL Server Windows NT 64 stopped working__ 2013-01-12

    I have Windows Vista Ultimate 64 Problems Reports, and Solution says my SQL Server Windows NT-64 bit Has stopped running see in details below: Problem signature Problem Event Name: APPCRASH Application Name: sqlservr.exe Application Version: 2007.100

  • SQL Server Windows NT stopped working 2012-11-23

    Product SQL Server Windows NT Problem Stopped working Date 11/10/2010 10:00 Status Report Sent Problem signature Problem Event Name: APPCRASH Application Name: sqlservr.exe Application Version: 2005.90.3042.0 Application Timestamp: 45cd955b Fault Mod

  • What are examples of SQL Server Query features/clauses that should be avoided? 2010-02-03

    What are examples of SQL Server Query features/clauses that should be avoided? Recently I got to know that the NOT IN clause degrades performance badly. Do you have more examples? --------------Solutions------------- The reason to avoid NOT IN isn't

  • Curious inconsistent behaviour from SQL Server in windowed function clauses? 2011-08-26

    Whilst asking another question, I discovered that SQL Server (happens both in 2005 and 2008) seems to have strange inconsistent behaviour when dealing with CASE statements in the clauses of windowed functions. The following code gives an error: decla

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.074 (s). 11 q(s)