Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

dynamic sort by and sort order

Posted on 2014-02-12
5
Medium Priority
?
689 Views
Last Modified: 2014-02-19
Hello,

I am on SQL Server 2012 trying to add a Dynamic ORDER BY clause.

	ORDER BY 
		CASE 
			WHEN @SortBy = 1 THEN LastRunTime 
                        WHEN @SortBy = 2 THEN Name 
		END			
		CASE 
			WHEN @SortOrder = 1 THEN ASC
			WHEN @SortOrder = 2 THEN DESC
		END

Open in new window


But it does not seem to let me specify the direction with the use of a CASE statement.

Can anyone please teach me a work around that does not involve dynamic sql, or having to  write twice the entire query using an IF.

If possible that is.

Thank you so much.
0
Comment
Question by:metropia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 2000 total points
ID: 39853615
SELECT ...,
   CASE
      WHEN @SortBy = 1 AND @SortOrder = 1 THEN ROW_NUMBER() OVER(ORDER BY LastRunTime ASC)
      WHEN @SortBy = 1 AND @SortOrder = 2 THEN ROW_NUMBER() OVER(ORDER BY LastRunTime DESC)
      WHEN @SortBy = 2 AND @SortOrder = 1 THEN ROW_NUMBER() OVER(ORDER BY Name ASC)
      WHEN @SortBy = 2 AND @SortOrder = 2 THEN ROW_NUMBER() OVER(ORDER BY Name DESC)
   END AS RowNumber  
FROM ...
ORDER BY RowNumber
0
 

Author Comment

by:metropia
ID: 39853746
Hi BriCrowe.

Thank you for your example.

Would you mind explaining real briefly what this part does:
ROW_NUMBER() OVER

Thank you much.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39853777
Basically it is calculating an index value or "row number' for each row based on the criteria within the OVER clause.  We use the CASE statement to vary the way we calculate the row number depending on your @SortBy and @SortOrder parameters.

If you end up having issues referencing RowNumber in the ORDER BY clause then you may need to use an ordinal reference.  For instance if the RowNumber is the first column in your SELECT then "ORDER BY 1"
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39853784
I'd take a different approach, specifying both ASC and DESC sort values but leaving the unneeded one blank/empty, so it won't affect the sort anyway.

That is, when the @SortOrder = 1, ASC, the DESC sort value will always be blank (''), and when the @SortOrder = DESC, the ASC sort value will always be blank.


ORDER BY
    CASE WHEN @SortOrder = 1 THEN
        CASE WHEN @SortBy = 1 THEN CONVERT(varchar(30), LastRunTime, 121)
                  WHEN @SortBy = 2 THEN Name
        END
        ELSE ''
        END ASC,
    CASE WHEN @SortOrder = 2 THEN
        CASE WHEN @SortBy = 1 THEN CONVERT(varchar(30), LastRunTime, 121)
                  WHEN @SortBy = 2 THEN Name
        END
        ELSE ''
        END DESC
0
 
LVL 32

Expert Comment

by:awking00
ID: 39854429
case when @sortby = 1 and @sortorder = 1 then LastRunTime ASC
     when @sortby = 1 and @sortorder = 2 then LastRunTime DESC
     when @sortby = 2 and @sortorder = 1 then Name ASC
     when @sortby = 2 and @sortorder = 2 then Name DESC
end
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question