ORDER BY and CASE

Question

I have the following script in SQL:

ALTER PROC [dbo].[getRequests]
@SortBy             VARCHAR(50) = 'Date'
AS
SELECT  [ReqDate], [RequestorOrg], [RequestCategory],
        [ReqDescription], [OrgCountVote]
ORDER BY 
        CASE WHEN @SortBy = 'Date (oldest first)'   THEN [ReqDate]          END ASC, [OrgCountVote] DESC,
        CASE WHEN @SortBy = 'Date (newest first)'   THEN [ReqDate]          END DESC, [OrgCountVote] ,
        CASE WHEN @SortBy = 'Number of Votes'       THEN [OrgCountVote]     END DESC, reqdate,
        CASE WHEN @SortBy = 'Organisation'          THEN [RequestorOrg]     END,
        CASE WHEN @SortBy = 'Category'              THEN [RequestCategory]  END;

The first two cases in ORDER BY have more than one column to sort on.

When I attempt to save the script, it gives me the following message:

Msg 169, Level 15, State 1, Procedure getRequests, Line 8
A column has been specified more than once in the order by list. Columns in the order by list must be unique.

How can I ensure that the columns are unique?

Answer 1

I figured out quite a dynamic way to do the ORDER BY. In my example below, I can add as many WHEN's as I wish, it will just step through them and pick up the relevant statements.

ORDER BY   
CASE WHEN @SortBy = 'Date (oldest first)'   THEN [ReqDateTime]      END ASC,  
CASE WHEN @SortBy = 'Date (oldest first)'   THEN [OrgCountVote]     END DESC,  
CASE WHEN @SortBy = 'Date (newest first)'   THEN [ReqDateTime]      END DESC,  
CASE WHEN @SortBy = 'Date (newest first)'   THEN [OrgCountVote]     END DESC,  
CASE WHEN @SortBy = 'Number of Votes'       THEN [OrgCountVote]     END DESC,   
CASE WHEN @SortBy = 'Number of Votes'       THEN [ReqDateTime]      END DESC,  
CASE WHEN @SortBy = 'Organisation'          THEN [RequestorOrg]     END,  
CASE WHEN @SortBy = 'Status'                THEN ReqStatus          END,  
CASE WHEN @SortBy = 'Category'              THEN [RequestCategory]  END;  

I didn't have to worry about the ELSE, as the parameter has a default value assigned to it, and therefore will always have one of these statements:

@SortBy             VARCHAR(50) = 'Status'  

Answer 2

The problem is the OrgCountVote field. The fields enclosed within the CASE statement are fine... at least, it worked fine on my test system (Sql Server 2008 R2). Even so, I'd recommend adding a default ELSE clause to each of those case conditions, and you should also know that this can result in some really awful query plans (bad performance). For this kind of thing, you'll get much better performance doing your ordering in the client code.

That said, you can accomplish what you need like this:

ORDER BY
  --first level
    CASE WHEN @SortBy = 'Date (oldest first)' THEN [ReqDate]
         WHEN @SortBy = 'Date (newest first)' THEN CAST(CAST([ReqDate]-'1970-01-01' AS decimal(38,10))*-24*60*60*1000+0.5 as bigint)
         WHEN @SortBy = 'Number of Votes'     THEN [OrgCountVote] * -1 
         WHEN @SortBy = 'Organisation'        THEN [RequestorOrg]
         WHEN @SortBy = 'Category'            THEN [RequestCategory]  END, 
  --second level
    CASE WHEN @SortBy = 'Date (oldest first)' THEN [OrgCountVote] * -1
         WHEN @SortBy = 'Date (newest first)' THEN [OrgCountVote]
         WHEN @SortBy = 'Number of Votes'     THEN reqdate
         ELSE NULL END

I need to explain the Date (newest first) entry for a moment. If you just do a simple datediff, you end up overflowing the integer type. All the rest of that long expression is just to turn the date field into a number that you can multiply by -1, and thus invert the sort order. Credit to this link for how to do it:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/51cfea50-f915-4bac-bf7d-d053329086cd/difference-of-dates-in-millisecond?forum=transactsql

If you don't need millisecond granularity (ie: you're only storing date values, with all zeroes in the time component) or if you don't care it's not perfect here and there (records at nearly the same time might be out of order, perhaps if this were be an extremely rare occurrence for your data), then you could greatly simplify that expression and just take, say, the number of seconds since the unix epoch or the number of minutes or days since the Sql Server epoch.

Answer 3

I suggest you to use this type of stored procedure:

ALTER PROC [dbo].[getRequests]
@SortBy             VARCHAR(50) = 'Date'
AS
BEGIN
DECLARE @sql varchar(max) 

SET @sql = 'SELECT  [ReqDate], [RequestorOrg], [RequestCategory], [ReqDescription], [OrgCountVote] ' +
           'ORDER BY ' +
                CASE @SortBy
                    WHEN 'Date (oldest first)' THEN '[ReqDate] ASC, [OrgCountVote] DESC'
                    WHEN 'Date (newest first)' THEN '[ReqDate] DESC, [OrgCountVote] '
                    WHEN 'Number of Votes'     THEN '[OrgCountVote] DESC, reqdate'
                    WHEN 'Organisation'        THEN '[RequestorOrg]'
                    WHEN 'Category'            THEN '[RequestCategory]'
                END
EXEC(@sql)
END

Answer 4

This may give you similar results if you have to use case:

ALTER PROC [dbo].[getRequests]
@SortBy             VARCHAR(50) = 'Date'
AS
SELECT  [ReqDate], [RequestorOrg], [RequestCategory],
    [ReqDescription], [OrgCountVote]
FROM requests
ORDER BY 
CASE 
WHEN @SortBy = 'Date (oldest first)' THEN Datediff(day, '2010-1-1', [ReqDate]) 
WHEN @SortBy = 'Date (newest first)'   THEN [ReqDate]          
WHEN @SortBy = 'Number of Votes'       THEN [RequestorOrg]     
WHEN @SortBy = 'Category'              THEN [RequestCategory]  
END DESC, 
CASE WHEN @SortBy = 'Date (newest first)'    THEN OrgCountVote
WHEN @SortBy = 'Number of Votes'       THEN [OrgCountVote] 
ELSE '' END

Again, you may NOT want to do it. And the performance may be effected.

Answer 5

You should change the logic from using the complicated ORDER BY list of columns to using IF clauses and specifying multiple select statements and ORDER BYs.

Like:

If @SortBy = 'Date....' then
Begin
    -- SQL with the single order by group...
End
Else if @SortBy =... Then...
Begin

End