{"id":1654,"date":"2013-08-27T13:18:11","date_gmt":"2013-08-27T13:18:11","guid":{"rendered":"http:\/\/bicortex.com\/?p=1654"},"modified":"2013-09-05T05:45:27","modified_gmt":"2013-09-05T05:45:27","slug":"how-to-implement-conditional-order-by-in-sql","status":"publish","type":"post","link":"http:\/\/bicortex.com\/bicortex\/how-to-implement-conditional-order-by-in-sql\/","title":{"rendered":"How To Implement Conditional Order By in SQL"},"content":{"rendered":"<p style=\"text-align: justify;\">Lately I have been working on a project where a client requested a data to be returned in a sorted order with the flexibility of being able to select a column which they wanted the data to be sorted by. This option, using pure vanilla T-SQL is rather complex to achieve using T-SQL as writing a stored procedure that, for example, underpins the SSRS report in the following fashion will not work.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE PROCEDURE dbo.Sort_Dynamically\r\n@SortColumn NVARCHAR (100),\r\n@SortDirection VARCHAR (4)\r\nAS\r\nBEGIN\r\n ... ORDER BY @SortColumn, @SortDirection\r\nEND\r\nGO\r\n<\/pre>\n<p style=\"text-align: justify;\">There are few different approaches that could provide the flexible sorting functionality, so let\u2019s start with putting together a sample data set we can execute the SQL code against.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nUSE MASTER\r\nGO\r\nIF (EXISTS(SELECT Name FROM SysDatabases WHERE name = 'SortDB' ))\r\nBEGIN\r\nALTER DATABASE &#x5B;SortDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE\r\nDROP DATABASE &#x5B;SortDB]\r\nEND\r\nGO\r\nCREATE DATABASE SortDB\r\nGO\r\nUSE SortDB\r\nGO\r\n\r\nIF EXISTS\r\n\t(SELECT  * FROM SortDB.dbo.sysobjects o\r\n    WHERE o.xtype in ('U')\r\n    and o.id = object_id(N'sortdb..temp_Table'))\r\nDROP TABLE Temp_Table\r\nGO\r\n\r\nSELECT TOP 1000\r\nkey_col\t\t\t\t\t= ROW_NUMBER() OVER (ORDER BY s1.&#x5B;object_ID]),\r\nobject_id\t\t\t\t= s1.&#x5B;object_id],\r\nname\t\t\t\t\t= s1.name COLLATE DATABASE_DEFAULT,\r\nobject_tp_description\t= s1.type_desc COLLATE DATABASE_DEFAULT,\r\nmodify_date\t\t\t\t= s1.modify_date\r\nINTO Temp_Table\r\nFROM sys.all_objects AS s1\r\nORDER BY s1.&#x5B;object_id];\r\n<\/pre>\n<p style=\"text-align: justify;\">Given that we now have five attributes to potentially order by, let\u2019s assume that all these will participate in the user selection option. Additionally, the user will want to ensure that the sort order will be one of the inputs\/parameters so all data can be sorted in either ascending or descending order. To accomplish this requirement I have two separate methods which can produce the required data sets \u2013 one using dynamic SQL and another using window function.<\/p>\n<h4 style=\"text-align: center;\">DYNAMIC SQL METHOD<\/h4>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE PROCEDURE dbo.Sort_Dynamically_dSQL\r\n@SortColumn NVARCHAR (128),\r\n@SortDirection VARCHAR (4)\r\nAS\r\nBEGIN\r\n\tSET NOCOUNT ON;\r\n\t--ENSURE THAT CORRECT SORTING DIRECTION IS ENTERED\r\n\tIF UPPER(@SortDirection) NOT IN ('ASC', 'DESC')\r\n\t\tBEGIN\r\n\t\t\tRAISERROR('Invalid entry for @SortDirection: %s', 11, 1, @SortDirection);\r\n\t\t\tRETURN -1;\r\n\t\tEND\r\n\t--ENSURE THAT CORRECT COLUMN NAME IS ENTERED\r\n\tIF LOWER(@SortColumn) NOT IN ('key_col', 'object_id', 'name', 'object_tp_description', 'modify_date')\r\n\t\tBEGIN\r\n\t\t\tRAISERROR ('Invalid entry for @SortColumn: %s', 11, 1, @SortColumn);\r\n\t\t\tRETURN -1;\r\n\t\tEND\r\n\tSET @SortColumn = QUOTENAME(@SortColumn);\r\n\r\n\tDECLARE @Sql NVARCHAR (MAX)\r\n\tSET @Sql = 'SELECT key_col, object_id, name, object_tp_description, modify_date\r\n\tFROM Temp_Table\r\n\tORDER BY' + @SortColumn + '' + @SortDirection + ';'\r\n\tEXEC sp_executesql @Sql\r\nEND\r\n<\/pre>\n<p style=\"text-align: justify;\">This dynamic SQL encapsulated in a stored procedure with a few validation lines is pretty self-explanatory. It dynamically creates a SELECT statement based on the two variables entered as per below. Window function method, again encapsulated in a stored procedure, uses a different approach where sorting is done by means of ROW_NUMBER() function, whereas sort direction is the result of a simple multiplication to determine if the order should be ascending or descending as per the code below.<\/p>\n<h4 style=\"text-align: center;\">WINDOW FUNCTION METHOD<\/h4>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE PROCEDURE dbo.Sort_Dynamically_RowNumber\r\n@SortColumn NVARCHAR (128),\r\n@SortDirection VARCHAR (4)\r\nAS\r\nBEGIN\r\n\tSET NOCOUNT ON;\r\n\tWITH x AS\r\n\t\t(SELECT key_col, object_id, name, object_tp_description, modify_date,\r\n\t\trn = ROW_NUMBER() OVER(ORDER BY CASE @SortColumn\r\n\t\tWHEN 'key_col' THEN RIGHT ('000000000000' + RTRIM (key_col),12)\r\n\t\tWHEN 'object_id' THEN RIGHT(COALESCE(NULLIF(LEFT(RTRIM(object_id),1),'-'),'0')\r\n\t\t+ REPLICATE ('0',23) + RTRIM(object_ID),24)\r\n\t\tWHEN 'key_col' THEN RIGHT (RTRIM (key_col),12)\r\n\t\tWHEN 'object_id' THEN RIGHT(RTRIM(object_ID),24)\r\n\t\tWHEN 'name' THEN name\r\n\t\tWHEN 'object_tp_description' THEN object_tp_description\r\n\t\tWHEN 'modify_date' THEN CONVERT (VARCHAR(25), modify_date, 120)\r\n\t\tEND) * CASE @SortDirection WHEN 'ASC' THEN 1 ELSE -1 END\r\n\t\tFROM Temp_Table)\r\n\tSELECT key_col, object_id, name, object_tp_description, modify_date\r\n\tFROM x\r\n\tORDER BY rn\r\nEND\r\n<\/pre>\n<p style=\"text-align: justify;\">Now we can execute any of the two stored procedures with parameters assigned as per the column we wish to sort by and the sort order i.e. ascending or descending.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\n--SORT BY key_col column IN DESCENDING ORDER\r\n--USING dbo.Sort_Dynamically_RowNumber STORED PROCEDURE\r\nEXEC dbo.Sort_Dynamically_RowNumber 'key_col', 'desc'\r\n\r\n--SORT BY modify_date column IN ASCENDING ORDER\r\n--USING dbo.Sort_Dynamically_dSQL STORED PROCEDURE\r\nEXEC dbo.Sort_Dynamically_dSQL 'modify_date', 'asc';\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Lately I have been working on a project where a client requested a data to be returned in a sorted order with the flexibility of being able to select a column which they wanted the data to be sorted by. This option, using pure vanilla T-SQL is rather complex to achieve using T-SQL as writing [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[32,5,1],"tags":[12,49],"class_list":["post-1654","post","type-post","status-publish","format-standard","hentry","category-how-tos","category-sql","category-uncategorized","tag-code","tag-sql"],"aioseo_notices":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/1654","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/comments?post=1654"}],"version-history":[{"count":15,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/1654\/revisions"}],"predecessor-version":[{"id":1672,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/1654\/revisions\/1672"}],"wp:attachment":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/media?parent=1654"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/categories?post=1654"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/tags?post=1654"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}