SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Dynamic ORDER BY
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 4

pnunbe
Starting Member

Italy
3 Posts

Posted - 09/05/2004 :  04:49:42  Show Profile  Reply with Quote
Hi,
your guess about the "order by" block expansion is surely better than mine; yet if I submit to QueryAnalizer a query containing a clause like:
order by null,null, ...
I get an error message stating that the same column appears multiple times in the "order by" clause.
So I am not yet sure about how your method works.
Go to Top of Page

epanther
Starting Member

USA
8 Posts

Posted - 01/20/2005 :  19:30:35  Show Profile  Reply with Quote
I read through all of this and while some of you may have found solutions, they are very confusing. The simplest solution is usually the best solution, so here is a simple way around the "dynamic order by" problems.

One column problems:
order by
case when @nSort = 1 then FirstName end asc,
case when @nSort = 2 then FirstName end desc,
case when @nSort = 3 then LastName end asc,
case when @nSort = 4 then LastName end desc

If you don't want to use the "asc" or "desc", just remove them and the extra lines to identify the order.

order by
case when @nSort = 1 then FirstName end,
case when @nSort = 2 then LastName end

Two column problems:
order by
case when @nSort = 1 then FirstName end asc ,LastName asc,
case when @nSort = 2 then FirstName end asc ,LastName desc,
case when @nSort = 3 then LastName end asc ,FirstName asc,
case when @nSort = 4 then LastName end desc ,FirstName asc

This solves the casting problems because they are different statements. I once had a big problem with this and found this solution, which worked. Most of the code I found on boards like this one didn't work at all.

Hope this saves someone a lot of trouble one day.

Panther
Go to Top of Page

dlievense
Starting Member

USA
2 Posts

Posted - 11/29/2005 :  13:06:16  Show Profile  Send dlievense an AOL message  Reply with Quote
I'm having an issue with this dynamic order by. I've got it working on a ton of other stored procs we have but in this specific case it's failing:

group by dateadd(dd, sm.ship_lead_time * -1, od.delivery_date)
order by
case
when @SortColumn = 0 then dateadd(dd, sm.ship_lead_time * -1, od.delivery_date) end asc,
case
when @SortColumn = 1 then sum(case when s.status_id = 1 then 1 else 0 end)
when @SortColumn = 7 then count(*) end desc

It tells me that the sm.ship_lead_time and od.delivery_date are not in the group by clause. But this works!

group by dateadd(dd, sm.ship_lead_time * -1, od.delivery_date)
order by
dateadd(dd, sm.ship_lead_time * -1, od.delivery_date),
case
when @SortColumn = 1 then sum(case when s.status_id = 1 then 1 else 0 end)
when @SortColumn = 7 then count(*) end desc

So this is JUST a problem with the dynamic order by clauses and specifically the dateadd function, since the sum & count work correctly.

I'm theorizing that the issue is specifically that dateadd is a function, where the other items are aggregate key words. I've tried using aliasing for the dateadd column and that doesn't work. I've also tried using ordinal numbers (i.e. order by 1) but that doesn't work either. Can anyone think of a way around this issue, without having to go the route of dynamically building a SQL String that you exec()? Thanks.

Senior Programmer/Analyst
CP Baker & Co
Go to Top of Page

drifter
Starting Member

USA
8 Posts

Posted - 11/29/2005 :  13:35:26  Show Profile  Visit drifter's Homepage  Reply with Quote
I wish I had time to figure it out, but a quick alternative may be to use a derived table to acquire your overall results and then order by a specific field instead of a function result.

For example,

select a.*
from (
select field1,
field2,
dateadd(dd, field3 * -1, field4) as calcField
group by
dateadd(dd, field3 * -1, field4)
) a
order by
case @sortColumn when 0 then a.calcField end asc,
...

I haven't tested that little sample, but I've done it before and it *should* give you what you are looking for.

--Drifter
Go to Top of Page

dlievense
Starting Member

USA
2 Posts

Posted - 11/29/2005 :  22:19:00  Show Profile  Send dlievense an AOL message  Reply with Quote
Drifter,

That worked thanks.

I'm curious though about the performance implications of doing something like this in general. It's always struck me as being kinda "hacky", and I've assumed that it would suffer a performance hit from doing something like this.

Thanks again though, you saved me a ton of aggrivation.

Senior Programmer/Analyst
CP Baker & Co
Go to Top of Page

drifter
Starting Member

USA
8 Posts

Posted - 11/30/2005 :  08:48:30  Show Profile  Visit drifter's Homepage  Reply with Quote
I'm not sure if you mean the performance of the derived table or the dynamic ordering, so I'll put in my 2 cents about both :-)

I have not run into any cases where using a derived table hurt my performance, and it has proven to be a very useful technique to simplify what can otherwise be quite a mess of a query using other techniques. In many cases I have found it to give me performance gains when I am able to heavily filter the derived table before joining it to other tables in the query it is contained within.

As for the dynamic ordering, I haven't needed to use it as much and as such I don't have very many comparison cases. I have not really noticed any performance problems when using this technique though. Each time I have used it is has been quite stable and predictable.


--Drifter
Go to Top of Page

Sreekumar
Starting Member

India
1 Posts

Posted - 01/14/2006 :  00:29:53  Show Profile  Reply with Quote
FOR Dynamic Orderby with ASC/DESC
USE northwind

DECLARE @SortCol tinyint
DECLARE @SortOrder tinyint
SET @SortOrder = 1
SEt @SortCol = 2

SELECT CompanyName,
ContactName,
ContactTitle
FROM Customers
ORDER BY CASE WHEN @SortOrder = 1 AND @SortCol = 1 THEN CompanyName
WHEN @SortOrder = 1 AND @SortCol = 2 THEN ContactName
END ASC,
CASE WHEN @SortOrder = 2 AND @SortCol = 1 THEN CompanyName
WHEN @SortOrder = 2 AND @SortCol = 2 THEN ContactName
END DESC



Sreekumar P PMP
Go to Top of Page

mpenner
Starting Member

1 Posts

Posted - 04/25/2006 :  13:37:41  Show Profile  Reply with Quote
I read the article and it had some great ideas. I, like many others here, had problems with sorting. However, among the solutions given I didn't find anything I liked.

In my situation I have a table destined for a web page and pdf with 12 columns, of which I want any of them to be sortable and a memory of like 4 columns, meaning I want an order by clause of something like ORDER BY columnB, columnF DESC, columnA, columnD DESC.

Some of the solutions given here would have required me to write huge amounts of case statements covering all remote possibilities, and others never accounted for multiple search criteria.

So, I actually used what seems to me to be really simple and the best of both worlds.

My entire query is pretty static, no dynamic where clause, so it is contained in a stored procedure. However, I dump the result into a temporary table. This takes advantage of all the benefits of stored procedures.

I then use the dynamic execution example given in the article and call a simple 'SELECT * FROM #Temp ORDER BY' + @OrderByClause statement.

This allows me to pass any order clause I want. My web page simply keeps track of all the columns the user clicks. Each time it amends the OrderByClause and passes it back to the stored procedure. It works great! The @OrderByClause is an optional parameter with a default value making it friendly to legacy code that may not implement the parameter.

Plus, my original stored procedure has only 6 extra lines of code to produce this new dynamic ordering.

I hope this helps anyone else.
Go to Top of Page

Rwk
Starting Member

Italy
1 Posts

Posted - 06/07/2006 :  10:51:00  Show Profile  Reply with Quote
for ASC and DESC, with compatibility with sorting of gridview (asp.net 2.0)

i found this:

ORDER BY
CASE
WHEN @sortexpression='CMNT_nome' THEN cmnt_nome
END ASC,
CASE
WHEN @sortexpression='CMNT_nome DESC' THEN cmnt_nome
END DESC
Go to Top of Page

BManTYA
Starting Member

6 Posts

Posted - 05/19/2007 :  02:45:52  Show Profile  Reply with Quote
I am fairly new to Stored Procedures and was wondering if there was a way to sort ASC but with NULLS at the end of the list. Here is the code I have so far.

ALTER PROCEDURE [dbo].[XBF_SelectListAll]
AS
/* SET NOCOUNT ON */

SELECT Gamertag, Game, Score, Profile, Avatar, Presence, Status, Zone, Reputation, LastSeen, Updated, UpdatedBy
FROM XBF_GamerData
ORDER BY
CASE Status
WHEN 'Online' THEN 1
WHEN 'Away' THEN 2
WHEN 'Offline' THEN 3
WHEN 'Unknown' THEN 4
WHEN 'Removed' THEN 5
ELSE NULL
END,
Game , Gamertag

RETURN

The only problem is that the NULLS for game are at the top of the sort and I want them at the bottom without having all the games in DESC order. Was thinking of...

CASE Game
WHEN 'Game IS NOT NULL' THEN 1
WHEN 'Game IS NULL' THEN 2
ELSE NULL
END,

When I try it it just ignores the game all together.
Go to Top of Page

drifter
Starting Member

USA
8 Posts

Posted - 05/21/2007 :  13:10:57  Show Profile  Visit drifter's Homepage  Reply with Quote
BManTYA,

You have 2 simple options to fix your first query.

The first option is to change your "else" section in the case statement to return a number higher than the rest, such as 6, rather than a NULL value. That will sort anything that isn't explicitly handled in your "when" clauses at the end. Effectively, that means NULL and any values not in your list of "when" clauses.

The second option is to use the isnull() function around the field name so you can convert the NULL values to something meaningful and then handle the value in your ordering case. For example, "order by case isnull(status, 'NO STATUS') when ..." then simply add a "when" clause to handle 'NO STATUS' with an appropriate sort value.

As for your 2nd query that doesn't work, it is not giving you the results you want because you are effectively comparing the value of Game to 'Game IS NOT NULL' and 'Game IS NULL' rather than NULL values. To fix that query, remove the single-quotes from the "when" clauses and remove "Game" from the case line. When using the field in each "when" clause, you don't need it in the beginning of the case.

--Drifter
Go to Top of Page

Zodiacal
Starting Member

1 Posts

Posted - 04/30/2008 :  02:25:33  Show Profile  Reply with Quote
anyone know how I can use a dynamic order by with a 'distinct' select statement? I receive the following error unless i put the case statement in the select clause. I Would think that would hinder the performance because it has to run the case statement for each record.

Error:

"ORDER BY items must appear in the select list if SELECT DISTINCT is specified."

Go to Top of Page

superc0ntra
Starting Member

Sweden
2 Posts

Posted - 01/28/2009 :  10:18:30  Show Profile  Reply with Quote
order by
CASE
WHEN @desc= 1 THEN integercolumn^-1
WHEN @asc= 1 THEN integercolumn
END

Works if datatype is numeric (at least with integers)
Go to Top of Page

jxrockwell
Starting Member

1 Posts

Posted - 03/08/2009 :  10:50:54  Show Profile  Reply with Quote
I've been using SQL similar to what is listed in this page to get a dynamic order by for some time.

My stored proc operates on tables that have a few, but not a ton (e.g. ~100k) records in them. I was recently attempting to do some performance troubleshooting on this query and have found the case statement in the order by is causing problems.

Here is the original code. In my case I was actually passing in the full string and then ordering as appropriate:


ORDER BY 
	CASE @SortExpression
		WHEN 'MyColA DESC' THEN MyColA 
	END DESC,
	CASE @SortExpression 
		WHEN 'MyColA ASC' THEN MyColA 
	END ASC,
	CASE @SortExpression 
		WHEN 'MyColB DESC' THEN MyColB 
	END DESC


I tried changing this to an int thinking it might be the varchar slowing it down?


ORDER BY 
	CASE @SortExpression
		WHEN 1 THEN MyColA 
	END DESC


But , that didn't work. It is however much faster if I take out the parameter all together. However, this obviously won't work because I need to be able to pass in the parameter to trigger the correct order by.


ORDER BY 
	CASE 1
		WHEN 1 THEN MyColA 
	END DESC


Has anyone else epxerienced this sort of thing?
Go to Top of Page

WaltonCreative
Starting Member

United Kingdom
1 Posts

Posted - 03/10/2009 :  04:45:35  Show Profile  Visit WaltonCreative's Homepage  Reply with Quote
Hi I am trying to sort dynamically using a link to add SortBy=Code etc to a URL.

I have tried adding your MySQL script, but keep getting an error with the DECLARE part.

Where EXACTLY do I put the DECLARE statement?

I am using Dreamweaver - below is the complete script for the page.

<?php require_once('Connections/chilstone_connection.php'); ?>
<?php
if (!isset($_SESSION)) {
session_start();
}
$MM_authorizedUsers = "";
$MM_donotCheckaccess = "true";

// *** Restrict Access To Page: Grant or deny access to this page
function isAuthorized($strUsers, $strGroups, $UserName, $UserGroup) {
// For security, start by assuming the visitor is NOT authorized.
$isValid = False;

// When a visitor has logged into this site, the Session variable MM_Username set equal to their username.
// Therefore, we know that a user is NOT logged in if that Session variable is blank.
if (!empty($UserName)) {
// Besides being logged in, you may restrict access to only certain users based on an ID established when they login.
// Parse the strings into arrays.
$arrUsers = Explode(",", $strUsers);
$arrGroups = Explode(",", $strGroups);
if (in_array($UserName, $arrUsers)) {
$isValid = true;
}
// Or, you may restrict access to only certain users based on their username.
if (in_array($UserGroup, $arrGroups)) {
$isValid = true;
}
if (($strUsers == "") && true) {
$isValid = true;
}
}
return $isValid;
}

$MM_restrictGoTo = "AdminLogInFailed.php";
if (!((isset($_SESSION['MM_Username'])) && (isAuthorized("",$MM_authorizedUsers, $_SESSION['MM_Username'], $_SESSION['MM_UserGroup'])))) {
$MM_qsChar = "?";
$MM_referrer = $_SERVER['PHP_SELF'];
if (strpos($MM_restrictGoTo, "?")) $MM_qsChar = "&";
if (isset($QUERY_STRING) && strlen($QUERY_STRING) > 0)
$MM_referrer .= "?" . $QUERY_STRING;
$MM_restrictGoTo = $MM_restrictGoTo. $MM_qsChar . "accesscheck=" . urlencode($MM_referrer);
header("Location: ". $MM_restrictGoTo);
exit;
}
?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
$colname_SectionFromURL_Recordset = "-1";
if (isset($_GET['Section'])) {
$colname_SectionFromURL_Recordset = $_GET['Section'];
}
$colname2_SectionFromURL_Recordset = "-1";
if (isset($_GET['Department'])) {
$colname2_SectionFromURL_Recordset = $_GET['Department'];
}
mysql_select_db($database_chilstone_connection, $chilstone_connection);
$query_SectionFromURL_Recordset = sprintf("DECLARE @SortOrder tinyint
SET @SortOrder = 2
SELECT *
FROM products
WHERE `Section` = colname OR `Department` = colname2
ORDER BY CASE WHEN @SortOrder = 1 THEN Code
WHEN @SortOrder = 2 THEN Name
ELSE Order", GetSQLValueString($colname_SectionFromURL_Recordset, "text"),GetSQLValueString($colname2_SectionFromURL_Recordset, "text"));
$SectionFromURL_Recordset = mysql_query($query_SectionFromURL_Recordset, $chilstone_connection) or die(mysql_error());
$row_SectionFromURL_Recordset = mysql_fetch_assoc($SectionFromURL_Recordset);
$totalRows_SectionFromURL_Recordset = mysql_num_rows($SectionFromURL_Recordset);
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<title>Chilstone ~ <?php echo $row_SectionFromURL_Recordset['Section']; ?></title>
<link href="css.css" rel="stylesheet" type="text/css" />


<script type="text/javascript">
<!--
function MM_preloadImages() { //v3.0
var d=document; if(d.images){ if(!d.MM_p) d.MM_p=new Array();
var i,j=d.MM_p.length,a=MM_preloadImages.arguments; for(i=0; i<a.length; i++)
if (a[i].indexOf("#")!=0){ d.MM_p[j]=new Image; d.MM_p[j++].src=a[i];}}
}

function MM_swapImgRestore() { //v3.0
var i,x,a=document.MM_sr; for(i=0;a&&i<a.length&&(x=a[i])&&x.oSrc;i++) x.src=x.oSrc;
}

function MM_findObj(n, d) { //v4.01
var p,i,x; if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) {
d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[i][n];
for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers[i].document);
if(!x && d.getElementById) x=d.getElementById(n); return x;
}

function MM_swapImage() { //v3.0
var i,j=0,x,a=MM_swapImage.arguments; document.MM_sr=new Array; for(i=0;i<(a.length-2);i+=3)
if ((x=MM_findObj(a[i]))!=null){document.MM_sr[j++]=x; if(!x.oSrc) x.oSrc=x.src; x.src=a[i+2];}
}


//-->
</script>
</head>
<body onload="MM_preloadImages('images/NavigationTop/navigationTop_01OVER.jpg','images/NavigationTop/navigationTop_02OVER.jpg','images/NavigationTop/navigationTop_03OVER.jpg','images/NavigationTop/navigationTop_04OVER.jpg','images/NavigationTop/navigationTop_05OVER.jpg','images/NavigationTop/navigationTop_06OVER.jpg','images/NavigationTop/navigationTop_07OVER.jpg','images/NavigationTop/navigationTop_08OVER.jpg','images/NavigationTop/navigationTop_09OVER.jpg','images/NavigationTop/navigationTop_10OVER.jpg','images/NavigationTop/navigationTop_11OVER.jpg')">


<div class="wholeSite">

<div id="whiteBackgroundFullWidth"></div>

<div id="sectionThumbs">
<table width="664" border="0" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF">


<tr>

<td class="adminShowHiddenProductsOnly"><strong> feature coming soon
>>>>         sort order:     </strong><a href="?SortBy=2">normal</a>     <a href="?SortBy=4">price</a>      <a href="?SortBy=Code">code</a>      <a href="?SortBy=8">hidden
first</a>      <a href="?SortBy=8">hidden last</a></td>

</tr>
<tr>
<td><?php do { ?>

<table width="151" border="0" cellpadding="0" cellspacing="0" class="adminSummaryTable">
<tr>
<td align="center" valign="top">

<table width="151" border="0" cellpadding="0" cellspacing="0" class="adminHoldingTable">
<tr>
<td align="center" valign="top">
<p><a href="AdminProduct.php?Code=<?php echo $row_SectionFromURL_Recordset['Code']; ?>" target="_self"><img src="images/ProductsThumbs/<?php echo $row_SectionFromURL_Recordset['Code']; ?>.jpg" border="0" title="click to edit details" /></a></p></td>
</tr>
</table>


<p><strong><?php echo $row_SectionFromURL_Recordset['Name']; ?></strong></p>
<p>£ <?php echo $row_SectionFromURL_Recordset['Price']; ?></p>
<p>Code <?php echo $row_SectionFromURL_Recordset['Code']; ?></p></td>
</tr>
</table>

<?php } while ($row_SectionFromURL_Recordset = mysql_fetch_assoc($SectionFromURL_Recordset)); ?></td>
</tr>
</table>




<div id="creditsSectionOnly">

</div>

</div>

<div id="adminShadowUnderLogo"></div>



</div>
<!--end of wholeSite-->


<?php require_once('AdminNavigationTop.php'); ?>
<?php require_once('AdminNavigationSideArchitectural.php'); ?>
<?php
mysql_free_result($SectionFromURL_Recordset);
?>

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/10/2009 :  04:47:47  Show Profile  Reply with Quote
for MySQL related queries please post in some MySQL forums like www.dbforums.com .this is ms sql server forum so solutions provided will be sql server specific
Go to Top of Page

weitzhandler
Yak Posting Veteran

64 Posts

Posted - 04/16/2009 :  20:59:15  Show Profile  Visit weitzhandler's Homepage  Send weitzhandler an ICQ Message  Reply with Quote
My problem is that I want to have the DESC or ASC conditional too:

SELECT * FROM Table ORDER BY CASE WHEN @Direction = 1 THEN DESC END


Shimmy
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37460 Posts

Posted - 04/16/2009 :  21:48:20  Show Profile  Visit tkizer's Homepage  Reply with Quote
Shimmy, that is handled with this approach. Make sure you check this whole thread.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

weitzhandler
Yak Posting Veteran

64 Posts

Posted - 04/16/2009 :  22:51:21  Show Profile  Visit weitzhandler's Homepage  Send weitzhandler an ICQ Message  Reply with Quote
Sorry, I have really no idea what you're talking about.
I just visited 3 advertizing links

However, the answer to my question is as follows:

SELECT * FROM Table ORDER BY CASE WHEN @Direction = 1 THEN -SortOrder else SortOrder END ASC


Shimmy
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37460 Posts

Posted - 04/16/2009 :  22:58:48  Show Profile  Visit tkizer's Homepage  Reply with Quote
Lol, I'm not talking about the links in my signature.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page
Page: of 4 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000