| Author |
 Topic  |
|
pnunbe
Starting Member
Italy
3 Posts |
 Posted - 09/05/2004 : 04:49:42
|
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. |
 |
|
|
epanther
Starting Member
USA
8 Posts |
Posted - 01/20/2005 : 19:30:35
|
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 |
|
|
|
dlievense
Starting Member
USA
2 Posts |
Posted - 11/29/2005 : 13:06:16
|
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 |
|
|
|
drifter
Starting Member
USA
8 Posts |
Posted - 11/29/2005 : 13:35:26
|
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 |
|
|
|
dlievense
Starting Member
USA
2 Posts |
Posted - 11/29/2005 : 22:19:00
|
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 |
|
|
|
drifter
Starting Member
USA
8 Posts |
Posted - 11/30/2005 : 08:48:30
|
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 |
|
|
|
Sreekumar
Starting Member
India
1 Posts |
Posted - 01/14/2006 : 00:29:53
|
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 |
|
|
|
mpenner
Starting Member
1 Posts |
Posted - 04/25/2006 : 13:37:41
|
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.  |
|
|
|
Rwk
Starting Member
Italy
1 Posts |
Posted - 06/07/2006 : 10:51:00
|
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 |
|
|
|
BManTYA
Starting Member
6 Posts |
Posted - 05/19/2007 : 02:45:52
|
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. |
|
|
|
drifter
Starting Member
USA
8 Posts |
Posted - 05/21/2007 : 13:10:57
|
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 |
|
|
|
Zodiacal
Starting Member
1 Posts |
Posted - 04/30/2008 : 02:25:33
|
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."
|
|
|
|
superc0ntra
Starting Member
Sweden
2 Posts |
Posted - 01/28/2009 : 10:18:30
|
order by
CASE
WHEN @desc= 1 THEN integercolumn^-1
WHEN @asc= 1 THEN integercolumn
END
Works if datatype is numeric (at least with integers) |
|
|
|
jxrockwell
Starting Member
1 Posts |
Posted - 03/08/2009 : 10:50:54
|
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? |
|
|
|
WaltonCreative
Starting Member
United Kingdom
1 Posts |
Posted - 03/10/2009 : 04:45:35
|
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);
?>
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 03/10/2009 : 04:47:47
|
| 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 |
|
|
|
weitzhandler
Yak Posting Veteran
64 Posts |
Posted - 04/16/2009 : 20:59:15
|
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 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
weitzhandler
Yak Posting Veteran
64 Posts |
Posted - 04/16/2009 : 22:51:21
|
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 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
Topic |
|
|
|
Article: Dynamic ORDER BY
