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

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 03/13/2002 :  13:38:18  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
Go to Top of Page

jg4smile
Starting Member

1 Posts

Posted - 03/18/2002 :  13:46:45  Show Profile  Reply with Quote
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.

Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 03/18/2002 :  15:18:53  Show Profile  Visit robvolk's Homepage  Reply with Quote
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...

Go to Top of Page

Blastrix
Posting Yak Master

208 Posts

Posted - 04/26/2002 :  01:10:14  Show Profile  Reply with Quote
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


Go to Top of Page

redlam
Starting Member

7 Posts

Posted - 07/09/2002 :  20:24:19  Show Profile  Send redlam an AOL message  Reply with Quote
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?

Go to Top of Page

numeroneq
Starting Member

Sweden
8 Posts

Posted - 07/22/2002 :  08:20:18  Show Profile  Reply with Quote
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
Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 07/22/2002 :  08:24:10  Show Profile  Visit robvolk's Homepage  Reply with 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)

Go to Top of Page

numeroneq
Starting Member

Sweden
8 Posts

Posted - 07/22/2002 :  08:51:16  Show Profile  Reply with Quote
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
Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 07/22/2002 :  09:14:47  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.

Go to Top of Page

numeroneq
Starting Member

Sweden
8 Posts

Posted - 07/22/2002 :  09:40:25  Show Profile  Reply with Quote
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
Go to Top of Page

numeroneq
Starting Member

Sweden
8 Posts

Posted - 07/22/2002 :  10:04:25  Show Profile  Reply with Quote
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
Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 07/22/2002 :  10:09:05  Show Profile  Visit robvolk's Homepage  Reply with Quote
This is a little uglier looking, but it should work:

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
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
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
ELSE Null END ASC,
CASE @order WHEN 'DESC' THEN
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
ELSE Null END DESC
RETURN


All it does is replace the sortCol reference with the actual CASE expression. Let me know if this works or not.

Go to Top of Page

numeroneq
Starting Member

Sweden
8 Posts

Posted - 07/22/2002 :  10:43:15  Show Profile  Reply with Quote
quote:

All it does is replace the sortCol reference with the actual CASE expression. Let me know if this works or not.



It works beautifully! But with a risk of sounding picky do you think there might be some way to trick it into using the precalculated column to avoid the duplicate effort.
I mean, it works using a view so it all depends on the order of evaluation of the different statements within the query.
Could there perhaps be some option to make CASE statements evaluate last or do we just have to accept the fact that there still is some work to be done even with todays powerful SQL parsers?!


//NumerOneQ
Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 07/22/2002 :  12:23:45  Show Profile  Visit robvolk's Homepage  Reply with Quote
The effort isn't "duplicated". In the ORDER BY clause, each of the two CASE expressions for ASC and DESC are mutually exclusive, so only one of them will be fully evaluated each time it executes. However, the Count() expressions in the SELECT list must be evaluated every time, regardless of the ORDER BY clause. Perhaps the SQL Server query optimizer will see the same expression and reuse it, but that would happen regardless of whether you used the column alias or not. I doubt it though, because the same expression occurs in different parts of the process chain (SELECT and ORDER BY) I don't think there's any way for this to be further optimized.

I don't know why you want to use a view, I don't think there's any benefit over the stored procedure except to simplify the code a little. I think the SP will work better because the plan will be cached; depending on how you write the view, it might use a different plan for ASC sorts vs. DESC and might need to recompile. I could be wrong though. You should try testing both the plain SP code and a view-based version to see how they both perform.

Go to Top of Page

numeroneq
Starting Member

Sweden
8 Posts

Posted - 07/22/2002 :  12:57:45  Show Profile  Reply with Quote
quote:

The effort isn't "duplicated"....However, the Count() expressions in the SELECT list must be evaluated every time, regardless of the ORDER BY clause.


Well, this was just what I was thinking about, the COUNT (in particular the one with the CASE statement) is printed in code three times and evaluated twice since it is contained both in the SELECT and in the ORDER statements which, the way I see it, should/could(?) be avoided by using an alias for reference.
I do however agree with you that the optimizer probably uses the cached statement and never performs the second evaluation but still I'm curios if a memory pointer (in this case the SQL Alias) wouldn't be quicker than a cache hit?! I don't know, but no matter what it would leave cleaner code for others to read using an alias if possible...that's all.

quote:
I don't know why you want to use a view....


I'm sorry if I expressed myself a bit unclear but no, I don't want to use a view. I would very much like to place everything directly into the SP in a optimized query to avoid having to create a view for each current "list w/ sort query" I am using in my procedures.
My reference to the view was only to point out that it seems that everything depends on the order of evaluation between aliases and CASE statements. That is - if the calculated column is placed in a view and then referenced to from a CASE statement everything works ok but this is not the way I want it to run. I would prefer if I could find a way to make aliases evaluate before CASE statements and that way be able to create clean and optimal code.

Anyway I would like to thank you very much for your time and I will now look into the execution plan for your suggested model and will most probably use it as my new standard for "list w/ sort" procedures!

Thank you very much for your assistance!


//NumerOneQ
Go to Top of Page

thechatking
Starting Member

1 Posts

Posted - 08/07/2002 :  08:19:51  Show Profile  Reply with Quote
Many thanks... this was just what I was after! :o)

Go to Top of Page

ryanm7780
Starting Member

1 Posts

Posted - 09/23/2002 :  17:53:54  Show Profile  Reply with Quote
It is amazing to me how difficult this is to do when using SQL Server. Does everyone realize that in Oracle all you have to do is include whatever order by statement you want, regardless of any distincts? If Oracle can pull it off, you would think that MS could have figured out how to rip them off by now. Oh well.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 09/23/2002 :  18:12:54  Show Profile  Visit robvolk's Homepage  Reply with Quote
quote:
It is amazing to me how difficult this is to do when using SQL Server. Does everyone realize that in Oracle all you have to do is include whatever order by statement you want, regardless of any distincts? If Oracle can pull it off, you would think that MS could have figured out how to rip them off by now. Oh well.
I guess Oracle decided to spend their time on that instead of implementing standard, ANSI SQL-92 defined LEFT/RIGHT JOIN syntax instead of using those idiotic, non-standard, and utterly FUBAR (+) signs. If Microsoft can pull it off, you'd think Larry would've ripped it off from them by now.

Go to Top of Page

jpaviles
Starting Member

USA
8 Posts

Posted - 12/16/2002 :  11:44:18  Show Profile  Reply with Quote
Here's my scenario.. The users have the abilitity to pick three fields
to sort the data by.. So I have three variables coming into my stored procedure, but all three can be from a list of six fields..

I can't code the case statements in the order by clause because
it won't let me list the same column names.

I have tried to code the case statements in the select statement
but it doesn't seem to be sorting my data.. I am posting my sql
to see if any one has any ideas..

select tmp2.*,
case @sort1
when 'P' then tmp2.product
when 'A' then tmp2.acctName
when 'H' then tmp2.basehmocd
when 'N' then tmp2.basentwrkid
when 'R' then tmp2.product
when 'T' then tmp2.state
else null
end as orderby1A,
case @sort1
when 'S' then tmp2.pmscore
else null
end as orderby1D,
case @sort2
when 'P' then tmp2.product
when 'A' then tmp2.acctName
when 'H' then tmp2.basehmocd
when 'N' then tmp2.basentwrkid
when 'R' then tmp2.product
when 'T' then tmp2.state
else null
end as orderby2A,
case @sort2
when 'S' then tmp2.pmscore
else null
end as orderby2D,
case @sort3
when 'P' then tmp2.product
when 'A' then tmp2.acctName
when 'H' then tmp2.basehmocd
when 'N' then tmp2.basentwrkid
when 'R' then tmp2.product
when 'T' then tmp2.state
else null
end as orderby3A,
case @sort3
when 'S' then tmp2.pmscore
else null
end as orderby3D
into #temp
from #temp2 tmp2
inner join ##temp1 tmp1 on tmp2.memberid = tmp1.memberid
order by orderby1A asc,
orderby1D desc , orderby2A asc, orderby2D desc, orderby3A asc, orderby3D desc


jpaviles
Go to Top of Page

jpaviles
Starting Member

USA
8 Posts

Posted - 12/16/2002 :  11:44:32  Show Profile  Reply with Quote
Here's my scenario.. The users have the abilitity to pick three fields
to sort the data by.. So I have three variables coming into my stored procedure, but all three can be from a list of six fields..

I can't code the case statements in the order by clause because
it won't let me list the same column names.

I have tried to code the case statements in the select statement
but it doesn't seem to be sorting my data.. I am posting my sql
to see if any one has any ideas..

select tmp2.*,
case @sort1
when 'P' then tmp2.product
when 'A' then tmp2.acctName
when 'H' then tmp2.basehmocd
when 'N' then tmp2.basentwrkid
when 'R' then tmp2.product
when 'T' then tmp2.state
else null
end as orderby1A,
case @sort1
when 'S' then tmp2.pmscore
else null
end as orderby1D,
case @sort2
when 'P' then tmp2.product
when 'A' then tmp2.acctName
when 'H' then tmp2.basehmocd
when 'N' then tmp2.basentwrkid
when 'R' then tmp2.product
when 'T' then tmp2.state
else null
end as orderby2A,
case @sort2
when 'S' then tmp2.pmscore
else null
end as orderby2D,
case @sort3
when 'P' then tmp2.product
when 'A' then tmp2.acctName
when 'H' then tmp2.basehmocd
when 'N' then tmp2.basentwrkid
when 'R' then tmp2.product
when 'T' then tmp2.state
else null
end as orderby3A,
case @sort3
when 'S' then tmp2.pmscore
else null
end as orderby3D
into #temp
from #temp2 tmp2
inner join ##temp1 tmp1 on tmp2.memberid = tmp1.memberid
order by orderby1A asc,
orderby1D desc , orderby2A asc, orderby2D desc, orderby3A asc, orderby3D desc


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