Article: Dynamic ORDER BY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Dynamic ORDER BY

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-01-21 : 20:31:38

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.

CaseyC
Starting Member

8 Posts

Posted - 2002-01-30 : 11:20:15

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?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-01-31 : 08:54:52

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.

ToddV
Posting Yak Master

218 Posts

Posted - 2002-01-31 : 09:07:17

You could also do this with a case statement:

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

jpaviles
Starting Member

8 Posts

Posted - 2002-03-12 : 16:46:08

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-12 : 16:47:18

Can you post your entire SQL statement?

jpaviles
Starting Member

8 Posts

Posted - 2002-03-13 : 13:11:25

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-13 : 13:29:23

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.

jpaviles
Starting Member

8 Posts

Posted - 2002-03-13 : 13:31:40

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

jpaviles
Starting Member

8 Posts

Posted - 2002-03-13 : 13:32:59

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-13 : 13:38:18

If you don't mind the extra column (don't seem to

), you can try this:

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,
CASE when @SortBy = "T" then P.ProductName
when @SortBy = "B" then B.LastName
end AS SortCol

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 SortCol

This adds an extra column (SortCol) that evaluates the same CASE expression, and you can ORDER BY it instead of the CASE expression. Since it's a regular column now the DISTINCT should NOT cause a problem.

Edited by - robvolk on 03/13/2002 14:49:47

jg4smile
Starting Member

1 Post

Posted - 2002-03-18 : 13:46:45

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

i had the same problem. the way i worked around it was to create a view with the "distinct" statement and then select from the view in my SP. it seemes really silly that sql server couldn't recognize that all of the fields were in the select list just because of the distinct keyword and a dynamic sort order statement.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-18 : 15:18:53

quote:
it seemes really silly that sql server couldn't recognize that all of the fields were in the select list just because of the distinct keyword and a dynamic sort order statement.

If you know how to fix or optimize that in SQL Server, I'm sure Microsoft would love to hear from you...

Blastrix
Posting Yak Master

208 Posts

Posted - 2002-04-26 : 01:10:14

I was able successfully implement some of the ORDER BY code found here, but can't get it working completely right. I have two columns that I want to sort by, one is varchar, the other is int. I originally tried casting the int field to varchar, but then that wouldn't sort correctly(1, 14, 1570, 3, 35, etc...). I further tried casting the integer field, only if attempting to sort by the varchar field, but that wouldn't work. Oddly enough though, if I try to sort by the int field, it works fine without casting anything anywhere. The code is below, if anyone has any idea how to get it all sorting correctly it would be most appreciated.

Thanks
Steve

 

ALTER  PROCEDURE sp_SelectLinksByCategory
	(
	@CategoryID int,
	@Viewable bit = 1,
	@Approved bit = 1,
	@OrderBy varchar(25),
	@SortDir varchar(4)
	)
AS

SELECT
	LinkID,
	LinkCategoryID,
	LinkName,
	LinkURL,
	LinkClicks,
	IsApproved,
	IsViewable,
	CASE
		WHEN @OrderBy = 'LinkName' AND @SortDir = 'ASC' THEN LinkName
		WHEN @OrderBy = 'LinkClicks' AND @SortDir = 'ASC' THEN LinkClicks
		ELSE NULL
	END AS SortASC,
	CASE
		WHEN @OrderBy = 'LinkName' AND @SortDir = 'DESC' THEN LinkName
		WHEN @OrderBy = 'LinkClicks' AND @SortDir = 'DESC' THEN LinkClicks
		ELSE NULL
	END AS SortDESC	
FROM
	tblLinks
WHERE
	LinkCategoryID = @CategoryID
	AND IsApproved = @Approved
	AND IsViewable = @Viewable
ORDER BY 
	SortASC, SortDESC DESC

redlam
Starting Member

7 Posts

Posted - 2002-07-09 : 20:24:19

Great article. Question though - in testing this solution I realized that SQL is using a clustered index scan instead of an index seek. Is this common behavior when using this or any other function in this manner? Is it wise to use this solution for larger tables or should it be limited to smaller datasets?

numeroneq
Starting Member

8 Posts

Posted - 2002-07-22 : 08:20:18

Continuing the dynamic order issue...

Wildthing's post worked like a charm but I still have ONE major issue - the order of evaluation for calculated fields! I cannot use calculated fields, or even renamed fields, in the CASE statement - "Invalid column name" is all I recieve for response!!

Please, does anyone know how to get around this last obstacle in my query?? Any input is much appreciated, thank you!

I have pasted my procedure below in working state with the troublesome lines remarked, if I remove the remarks it won't compile:


Alter Procedure dbo.SP3022_GetParticipantCount(
  @OrderField As varchar(20), 
  @Order As varchar(4))
AS
  SELECT l.name, 
         (SELECT COUNT(participantid) 
          FROM T3022_participants t 
          WHERE t.locationid = c.locationid) AS total, 
         (SELECT COUNT(participantid) 
          FROM T3022_participants f 
          WHERE f.locationid = c.locationid AND options = 1) AS food
  FROM T3022_participants c
  INNER JOIN T3022_locations l ON c.locationid = l.locationid
  ORDER BY 
  CASE WHEN @OrderField = 'name' AND 
            @Order = 'ASC' THEN l.name
     --WHEN @OrderField = 'total' AND 
          --@Order = 'ASC' THEN total
     --WHEN @OrderField = 'food' AND 
          --@Order = 'ASC' THEN food
  END ASC,
  CASE WHEN @OrderField = 'name' AND 
            @Order = 'DESC' THEN l.name
     --WHEN @OrderField = 'total' AND 
          --@Order = 'DESC' THEN total
     --WHEN @OrderField = 'food' AND 
          --@Order = 'DESC' THEN food
  END DESC
RETURN

//NumerOneQ

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-22 : 08:24:10

You have to include the full expression used in the calculated column. AFAIK the only way to get around this is to use a defined sort column (check my earlier reply in this thread)

numeroneq
Starting Member

8 Posts

Posted - 2002-07-22 : 08:51:16

quote:

You have to include the full expression used in the calculated column. AFAIK the only way to get around this is to use a defined sort column (check my earlier reply in this thread)

Thanx, but including the full calculated expression in the ORDER BY clause would mean to repeat the same query twice and that kind of seems to take the edge of the performace enchancement I think.

Placing the CASE statement in a "sort column" unfortunately produces the same error with "Invalid column name" when referencing the calculated columns as it does when used directly in the ORDER BY clause...

Anyother ideas or inputs?!

//NumerOneQ

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-22 : 09:14:47

Alter Procedure dbo.SP3022_GetParticipantCount(
@OrderField As varchar(20),
@Order As varchar(4))
AS
SELECT l.name, Count(*) AS total,
Count(CASE Options WHEN 1 THEN 1 ELSE Null END) AS food,
CASE @OrderField
WHEN 'name' THEN l.name
WHEN 'total' THEN Str(Count(*), 10,0)
WHEN 'food' THEN Str(Count(CASE Options WHEN 1 THEN 1 ELSE Null END), 10, 0)
END AS SortCol
FROM T3022_participants c
INNER JOIN T3022_locations l ON c.locationid = l.locationid
GROUP BY l.name
ORDER BY CASE @order WHEN 'ASC' THEN sortCol ELSE Null END ASC,
CASE @order WHEN 'DESC' THEN sortCol ELSE Null END DESC
RETURN

I haven't tested this, so if it totally blows up, or if it's close but not quite perfect, let me know.

numeroneq
Starting Member

8 Posts

Posted - 2002-07-22 : 09:40:25

quote:

I haven't tested this, so if it totally blows up, or if it's close but not quite perfect, let me know.

Thanx, I like the suggestion to COUNT in native instead of in nested SELECT's but unfortunately not even that gives the advance on the CASE clause which seems still to be evaluated first of all, or at least before the calculated columns. Thus the problem remains the same - only true columns may exists in a CASE clause, the error message says "Invalid column name sortCol"!

If I could somehow alter the order of evaluation within the query...

//NumerOneQ

numeroneq
Starting Member

8 Posts

Posted - 2002-07-22 : 10:04:25

quote:

If I could somehow alter the order of evaluation within the query...

Just wanted to add that it works fine when using a VIEW but I would really like to avoid having to create views for each "list w/ sort" query I have, since if we can get this to work I would like to optimize ALL my current listpage SP's. I currently use EXECUTE(querystring) within the procedures which obviously is not the optimal way of working so please keep kicking me ideas - great many thanx!

//NumerOneQ

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources