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
Next Page
Author Previous Topic Topic Next Topic
Page: of 4

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 01/21/2001 :  20:31:38  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
We've got another article chock full of SQL goodness from Garth. This one covers creating a dynamic ORDER BY clause for your SELECT statements. It's a follow up to his earlier article on dynamic WHERE clauses.

Article Link.

Anonymous
Starting Member

0 Posts

Posted - 01/25/2001 :  16:17:03  Show Profile  Reply with Quote
dynamic where and order by clauses

i liked the articles and found them giving new insight into TSQL. i could appreciate it because in a recent project we had a similar requirement but had to make do with dynamic SQL.

looking forward to more such articles !

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 01/28/2001 :  05:12:59  Show Profile  Reply with Quote
What about dynamic asc or desc?

This technique is great. However, how would you handle dynamically generating the "asc" or "desc" keyword after the order by, since you cannot place two cases one after the other (at least I don't know how to do it; something like:

ORDER BY
CASE
WHEN @sortColumn = 'CustomerID' THEN CustomerID
WHEN @sortColumn = 'CompanyName' THEN CompanyName
ELSE ContactName
END

CASE
WHEN @sortOrder = 'asc' THEN asc
WHEN @sortOrder = 'desc' THEN desc
END


fails.

The only solution left looks like using dynamic SQL...

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 01/30/2001 :  18:00:29  Show Profile  Reply with Quote
Dynamic ORDER BY doesn't work for mixed data types? Or character datatypes?

Note the code below. The CASE statment doesn't like the mixed data types. It seems to work with dates and integers, but throw in a character value and I get an error such as:

Server: Msg 241, Level 16, State 1, Line 8
Syntax error converting datetime from character string.

Any help there?


/*
create table asset (asset_id int, title varchar(255), start_date datetime)
*/

DECLARE @sortColumn VARCHAR(255)
SELECT @sortColumn = 'title'

SELECT * FROM asset
ORDER BY
CASE
WHEN @sortColumn = 'title' THEN title
WHEN @sortColumn = 'start_date' THEN start_date
WHEN @sortColumn = 'asset_id' THEN asset_id
ELSE title
END

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 02/06/2001 :  03:01:19  Show Profile  Reply with Quote
Nice basic article

This is a useful technique, but you left out one caveat:

Every possible result of the case expression needs to have the same datatype - otherwise when you want to optionally sort on an Int field or a Varchar field, you'll have problems, because SQL Server seems always to decide to convert all the expressions to a numeric datatype, and complains that the character column isn't numeric.

The way to get around this is to do an explicit type conversion, using the convert or cast functions to convert the THEN expressions to a common datatype (generally varchar).

eg:
...
ORDER BY CASE WHEN @var = 1 THEN VarcharCol
WHEN @var = 2 THEN CAST(NumericCol AS VARCHAR(50))
END

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 02/15/2001 :  06:13:52  Show Profile  Reply with Quote
Heh, it's not easy

But what about order different data types such as varchar or integer?
I try it and it's not work. Exeptions occured when I order by ID and Description columns in my table.

Asc and Desc work correctly, but both datatypes... :-/

PS: I try to convert datatypes, but it longer not work.



Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 02/17/2001 :  20:54:27  Show Profile  Reply with Quote
Here's how I worked around it

I couldn't get the dynamic asc/desc to work, either.

So here's a workaround, when using a front-end like VB or ASP to display the data.

Perform the dynamic order by in the SQL, and bring the recordset into an array. With ADO, you can use GetRows.

Then, if the user wants Descending order, traverse the array backwards. Otherwise, traverse it forwards.

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 02/28/2001 :  16:59:58  Show Profile  Reply with Quote
Very useful article. Thanks.

Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 05/29/2001 :  21:48:01  Show Profile  Visit Merkin's Homepage  Reply with Quote
I have just used this technique and figured out the datatype issue (casting everything as a varchar) but can not get the dynamic ASC or DESC to work.

Has anyone managed to get this happening, I can do it in the ASP page but I don't want to be beaten




Damian
Go to Top of Page

jcelko
Esteemed SQL Purist

USA
547 Posts

Posted - 06/05/2001 :  16:14:12  Show Profile  Visit jcelko's Homepage  Reply with Quote
A few problems with some of the postings in this thread. ORDER BY cannot takea an expresssion in Standard SQL-92, so you have to create a column with the CASE expression in the SELECT list, name it, then ORDER BY it if you want portable code.

ASC and DESC are easy. Create two columns and use a flag to pick which one is set to NULL.

SELECT ..
CASE WHEN @sort_col = 'A'
AND @sort_order = 'ASC'
THEN A
WHEN @sort_col = 'B'
AND @sort_order = 'ASC'
THEN B
ELSE NULL END AS sort_1,
CASE WHEN @sort_col = 'A'
AND @sort_order = 'DESC'
THEN A
WHEN @sort_col = 'B'
AND @sort_order = 'DESC'
THEN B
ELSE NULL END AS sort_2
FROM Foobar
WHERE ..
ORDER BY sort_1, sort_2;


--CELKO--


--CELKO--
Joe Celko, SQL Guru
Go to Top of Page

Wildthing
Starting Member

4 Posts

Posted - 06/05/2001 :  16:39:39  Show Profile  Reply with Quote
To get around the datatype problem, use seperate group CASE statements for each datatype.

ORDER BY
CASE WHEN @SORTBY = 1 THEN Numeric1
WHEN @SORTBY = 2 THEN Numeric2
END,
CASE WHEN @SORTBY = 3 THEN VarChar1
WHEN @SORTBY = 4 THEN VarChar2
END DESC,
CASE WHEN @SORTBY = 5 THEN DateTime1
END

Note that each case block is seperated by a comma (after END) except the last block. Also note that you can use ASC or DESC for each CASE statement group.

Go to Top of Page

imagine___
Starting Member

USA
2 Posts

Posted - 07/09/2001 :  04:58:03  Show Profile  Send imagine___ a Yahoo! Message  Reply with Quote
This is an extension to Wildthing's post... Thanks Wildthing it really helped.

For those searchers that like to search on multiple columns, here is what I came up with. This has been tested, and works fine.

 

CREATE PROCEDURE pr_NiceLittleSortingProcedure
/** Default is by last, first names*/
@orderby AS INT = 1
AS

SELECT *
FROM t_Person p
WHERE ....
ORDER BY
/** All the Ascending orders*/
CASE @orderby
WHEN 1
THEN
p.s_lastname+ ', ' +p.s_firstname
/**121 = yyyy-mm-dd hh:mi:ss.mmm(24h)*/
WHEN 3
THEN
CONVERT(char(30),p.d_birth,121) + ' , ' + p.s_lastname + ' , ' +p.s_firstname
END ASC,
/** all the descending orders */
CASE @orderby
WHEN 2
THEN
p.s_lastname+ ', ' +p.s_firstname
/**121 = yyyy-mm-dd hh:mi:ss.mmm(24h)*/
WHEN 4
THEN
CONVERT(char(30),p.d_birth,121) + ' , ' + p.s_lastname + ' , ' +p.s_firstname
END DESC



Hope this helps... cheers.

--------------------------------
include java.util.EvilGerman;
Go to Top of Page

CaseyC
Starting Member

USA
8 Posts

Posted - 01/30/2002 :  11:20:15  Show Profile  Reply with Quote
I am somewhat of a novice at SQl, but I was wondering if there is a capability to expand upon Dynamic ORDER BY statements to order tables within a field, ex.

If I had a table ~90,000 records and one of the fields is STATUS. There are only one of five possibilties of STATUS (in this case ('Sold', 'Committed', 'In Progress', 'Lost', 'Not Pursued') and I want them ordered in that order (which is neither ASC or DSC) is there a way to dynamically order those?


Edited by - CaseyC on 08/01/2006 19:21:19
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 01/31/2002 :  08:54:52  Show Profile  Reply with Quote
my view would be no.....am open to correction....but joining to a temp(or permantent table) which would have the following rows would work.

'Sold' 1
'Committed' 2
'In Progress' 3
'Lost' 4
'Not Pursued' 5

where the numbers are the sort order...upside to this method is that by changing the numerical values, you can change the order without changing code.

Go to Top of Page

ToddV
Posting Yak Master

USA
218 Posts

Posted - 01/31/2002 :  09:07:17  Show Profile  Reply with Quote
You could also do this with a case statement:

ORDER BY Case Col1 WHEN 'sold' THEN 1 WHEN 'Committed' THEN 2 ...END



Go to Top of Page

jpaviles
Starting Member

USA
8 Posts

Posted - 03/12/2002 :  16:46:08  Show Profile  Reply with Quote
I am trying to use the dynamic order by and can't seem to get it to work.. Is this only for SQL 2000?

The error message I am getting is that
"order by items must appear in the select list if SELECT DISTINCT is specified"

I am including the fields so I don't get this at all.. Can this not be used with a DISTINCT??

TIA

jpaviles
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 03/12/2002 :  16:47:18  Show Profile  Visit robvolk's Homepage  Reply with Quote
Can you post your entire SQL statement?

Go to Top of Page

jpaviles
Starting Member

USA
8 Posts

Posted - 03/13/2002 :  13:11:25  Show Profile  Reply with Quote
CREATE Procedure sp_RPT_get_ProductsByTopic

(
@MainTopics varchar(255),
@MinorTopics varchar(255),
@SubMinorTopics varchar(255),
@ProjectStatuses varchar(255),
@ProfileID int,
@SortBy varchar(255)
)

AS

SELECT DISTINCT P.ProductName AS 'ProductName',
P.ProductID AS 'ProductID',
P.OwnerID AS 'OwnerID',
S.StatusDesc AS 'ProductStatusDesc',
S.StatusClass AS 'ProductStatusClass',
ISNULL(CONVERT(varchar(25),P.DateCompleted),'') AS 'DateCompleted',
P.BriefDescription AS 'BriefDescription',
B.LastName + ', ' + B.FirstName AS 'BusinessContactName',
B.EmailAddress AS 'BusinessContactEmail',
P.TechnicalContactID AS 'TechnicalContactID',
T.LastName + ', ' + T.FirstName AS 'TechnicalContactName',
T.EmailAddress AS 'TechnicalContactEmail',
P.DataSources AS 'DataSources',
P.LOBs AS 'LOBs',
P.Keywords AS 'Keywords',
A.BusinessAreaDesc AS 'BusinessAreaDesc',
P.ProjectOutputLink AS 'ProductOutputLink',
V.PreviewID AS 'PreviewID',
CASE
WHEN (R.Status=2 AND Y.ReportTypeDesc<>'WebFOCUS')
OR (I.HasUniversalAccess=1 AND Y.ReportTypeDesc<>'WebFOCUS')
OR (Y.ReportTypeDesc='WebFocus' AND J.reportid IS NOT NULL) THEN 1
ELSE 0
END AS 'CanView',
P.ProductName,
B.LastName,
P.LastUpdated


FROM Reporting_Products P
INNER JOIN Reporting_Statuses S ON P.ProjectStatusID = S.StatusID
INNER JOIN Reporting_BusinessContacts B ON P.BusinessContactID = B.BusinessContactID
INNER JOIN Reporting_TechnicalContacts T ON P.TechnicalContactID = T.TechnicalContactID
INNER JOIN Reporting_BusinessAreas A ON P.BusinessAreaID = A.BusinessAreaID
INNER JOIN Reporting_ReportTypes Y ON P.ReportTypeID = Y.ReportTypeID
LEFT OUTER JOIN Reporting_Report_User_Join J ON P.ProductID = J.reportid
AND @ProfileID = J.midsuid
LEFT OUTER JOIN MIDSU_Profile_GroupRequests R ON A.iHealthSectionID = R.Group_ID
AND @ProfileID = R.MIDSU_ID
LEFT OUTER JOIN iHealth_Sections I ON A.iHealthSectionID = I.SectionID
LEFT OUTER JOIN Reporting_ProductMainTopics M ON P.ProductID = M.ProductID
LEFT OUTER JOIN Reporting_ProductMinorTopics N ON P.ProductID = N.ProductID
LEFT OUTER JOIN Reporting_ProductSubMinorTopics U ON P.ProductID = U.ProductID
LEFT OUTER JOIN Reporting_Previews V ON P.ProductID = V.ProductID

WHERE (PATINDEX('%,' + CONVERT(varchar(255), M.MainTopicID) + ',%', ',' + @MainTopics + ',') <> 0 OR @MainTopics = '-1')
AND (PATINDEX('%,' + CONVERT(varchar(255), N.MinorTopicID) + ',%', ',' + @MinorTopics + ',') <> 0 OR @MinorTopics = '-1')
AND (PATINDEX('%,' + CONVERT(varchar(255), U.SubMinorTopicID) + ',%', ',' + @SubMinorTopics + ',') <> 0 OR @SubMinorTopics = '-1')
AND (PATINDEX('%,' + CONVERT(varchar(255), P.ProjectStatusID) + ',%', ',' + @ProjectStatuses + ',') <> 0 OR @ProjectStatuses = '-1')
AND P.IsAccepted = 1

order by CASE when @SortBy = "T" then P.ProductName
when @SortBy = "B" then B.LastName
end






jpaviles
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 03/13/2002 :  13:29:23  Show Profile  Visit robvolk's Homepage  Reply with Quote
The problem is that B.LastName does not appear by itself in the SELECT list, you have it in the expression "B.LastName + ', ' + B.FirstName". If you put this expression in the ORDER BY:

order by CASE when @SortBy = "T" then P.ProductName
when @SortBy = "B" then B.LastName + ', ' + B.FirstName
end


...you should be fine.

Go to Top of Page

jpaviles
Starting Member

USA
8 Posts

Posted - 03/13/2002 :  13:31:40  Show Profile  Reply with Quote
That is not it either..

I tried it and it didn't work..

I tried it only with the first statement and that won't compile either..

jpaviles
Go to Top of Page

jpaviles
Starting Member

USA
8 Posts

Posted - 03/13/2002 :  13:32:59  Show Profile  Reply with Quote
I also added the three fields that I needed to order by at the end of the select so they are by themselves and without an alias..

jpaviles
Go to Top of Page
Page: of 4 Previous Topic Topic Next Topic  
Next 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.22 seconds. Powered By: Snitz Forums 2000