April 24, 2012 at 4:39 pm
Hello All,
Can I dynamically sort ascending or descending using a parameter. I'd like my users to select a 'Top' or 'Bottom' flag and then see the top 3 items on either side of my data set.
I don't want to use dynamic SQL.
Would this technique negate the use of an index (if it were to work)?
Non functioning code below. Thanks to anyone kind enough to offer assistance.
DECLARE @DynamicSort varchar(6)
SET @DynamicSort = 'Top'
--SET @DynamicSort = 'Bottom'
CREATE TABLE #Test (Test int)
INSERT INTO #Test VALUES(1)
INSERT INTO #Test VALUES(2)
INSERT INTO #Test VALUES(3)
INSERT INTO #Test VALUES(4)
INSERT INTO #Test VALUES(5)
INSERT INTO #Test VALUES(6)
INSERT INTO #Test VALUES(7)
INSERT INTO #Test VALUES(8)
INSERT INTO #Test VALUES(9)
INSERT INTO #Test VALUES(10)
SELECT
TOP 3 *
FROM #Test
ORDER BY
CASE @DynamicSort
WHEN 'Top' THEN Test DESC
ELSE Test ASC
END
DROP TABLE #Test
April 24, 2012 at 4:49 pm
You would better off be writing it in IF...ELSE block. THat would produce a decent plan always.
But if u insist in using single query to do that, then u can try this
;with cte as
(
--1000000000 is a random number
-- it should be huge enuf to be higher than
-- the max value of in the column
select t.* , revorder = 1000000000 - t.Test
from #Test t
)
select top 3 c.Test
from cte c
order by case when @DynamicSort = 'Top' then c.revorder
else c.Test
end
April 24, 2012 at 5:15 pm
I have to throw out a blanket-statement here...sorting is expensive and should be left to the presentation layer where possible. The key being where possible, which leaves me an out for valid uses of ORDER BY within the data layer, of which there are some 😀
Consider what happens in your application when the user checks the box again, do you go back to SQL Server to get the same resultset, just sorted in a different way? I prefer to cache the resultset in the application layer, sort and re-sort as needed, and re-present. Just my two cents.
If you're going with the SQL solution I would go for an IF...Block or sp_executesql.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 24, 2012 at 5:41 pm
Chrissy,
Is this to allow passing the sorting components down to a pagination proc? That's the time I see items like this the most often. If it is, there's a few different approaches to it, but a better idea of the # of ordering parameters you want them to be able to pass and the like would help clear up the discussion if that's what you're doing.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 25, 2012 at 6:54 am
ColdCoffee (4/24/2012)
You would better off be writing it in IF...ELSE block. THat would produce a decent plan always.But if u insist in using single query to do that, then u can try this
;with cte as(
--1000000000 is a random number
-- it should be huge enuf to be higher than
-- the max value of in the column
select t.* , revorder = 1000000000 - t.Test
from #Test t
)
select top 3 c.Test
from cte c
order by case when @DynamicSort = 'Top' then c.revorder
else c.Test
end
If you take this approach the "random number" is irrelevant. In fact, I would just leave it out and use the unary negative.
CASE WHEN @DynamicSort = 'Top' THEN -c.Test ELSE c.Test END
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 25, 2012 at 8:12 am
Thanks all. I think having a usable execution plan is going to take precedent in this case so an IF...ELSE block
would be my best bet.
This is not a paging attempt, really just an attempt to minimize the amount of code to be maintained. but the performance tradeoff in using a dynamic order by just does not seem worth it.
Thanks again for the education...
April 25, 2012 at 8:18 am
Chrissy321 (4/25/2012)
Thanks all. I think having a usable execution plan is going to take precedent in this case so an IF...ELSE blockwould be my best bet.
This is not a paging attempt, really just an attempt to minimize the amount of code to be maintained. but the performance tradeoff in using a dynamic order by just does not seem worth it.
It sounds like you have a way forward you have deemed acceptable and I am happy about that. I think an if...block will serve you well here but I have to ask, what trade off? As i understand it we can get good, reusable execution plans using sp_executesql.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 25, 2012 at 8:39 am
The trade off I was referring to would be in code maintenance. Using IF>ELSE I'd have two largely identical blocks of code, one ACS and one DESC. Changes would need to be made to both. I wasn't referring to any trade-off between IF>ELSE and sp_executesql but between IF>ELSE and a dynamic ORDER BY statement.
Thanks.
April 25, 2012 at 8:51 am
Makes sense. Thanks for clarifying.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 25, 2012 at 8:54 am
First, I would say that you should have to separate stored procedures called by a master stored procedure depending on how you want the data sorted.
With that, I do have a solution based solely off your sample data:
DECLARE @DynamicSort varchar(6)
SET @DynamicSort = 'Top'
--SET @DynamicSort = 'Bottom'
CREATE TABLE #Test (Test int)
INSERT INTO #Test VALUES(1)
INSERT INTO #Test VALUES(2)
INSERT INTO #Test VALUES(3)
INSERT INTO #Test VALUES(4)
INSERT INTO #Test VALUES(5)
INSERT INTO #Test VALUES(6)
INSERT INTO #Test VALUES(7)
INSERT INTO #Test VALUES(8)
INSERT INTO #Test VALUES(9)
INSERT INTO #Test VALUES(10)
SELECT
TOP 3 *
FROM #Test
ORDER BY
CASE @DynamicSort
WHEN 'Top' THEN -1
ELSE 1 END * Test;
DROP TABLE #Test
GO
DECLARE @DynamicSort varchar(6)
--SET @DynamicSort = 'Top'
SET @DynamicSort = 'Bottom'
CREATE TABLE #Test (Test int)
INSERT INTO #Test VALUES(1)
INSERT INTO #Test VALUES(2)
INSERT INTO #Test VALUES(3)
INSERT INTO #Test VALUES(4)
INSERT INTO #Test VALUES(5)
INSERT INTO #Test VALUES(6)
INSERT INTO #Test VALUES(7)
INSERT INTO #Test VALUES(8)
INSERT INTO #Test VALUES(9)
INSERT INTO #Test VALUES(10)
SELECT
TOP 3 *
FROM #Test
ORDER BY
CASE @DynamicSort
WHEN 'Top' THEN -1
ELSE 1 END * Test;
DROP TABLE #Test
GO
April 25, 2012 at 9:19 am
Chrissy321 (4/25/2012)
The trade off I was referring to would be in code maintenance. Using IF>ELSE I'd have two largely identical blocks of code, one ACS and one DESC. Changes would need to be made to both. I wasn't referring to any trade-off between IF>ELSE and sp_executesql but between IF>ELSE and a dynamic ORDER BY statement.Thanks.
Just a suggestion. If you are creating a block of code in the application that allows the selection of a parameter, that needs to be maintained. It would not change the maintenance of the code to have that same block do the sorting itself once the data is retrieved from the database. In fact, if you are using the .NET framework, there is a simple control for this that requires no code maintenance.
Jared
CE - Microsoft
April 25, 2012 at 9:31 am
The code maintenance I was referring to is a stored procedure, server side, not client side.
I need the sorting server side since I am using the top keyword. My client is SSRS. I wouldn't want to pull tens of thousands of records to SSRS and then sort and filter there.
My report parameter is 'Top 3' or 'Bottom 3'. I'll then call the stored procedure and depending on the parameter will call the appropriate IF ELSE block in the stored procedure.
I'm anticipate my users will next want to dynamically set the number of top or bottom records returned. Show me top 10 rather than top 3.
I think my approach here would be limit the number of records, say 100. Hardcode 100 in my proc, always return 100 records to SSRS and then sort and filter the number of records to match my users record# parameter.
April 25, 2012 at 9:41 am
Chrissy321 (4/25/2012)
The code maintenance I was referring to is a stored procedure, server side, not client side.I need the sorting server side since I am using the top keyword. My client is SSRS. I wouldn't want to pull tens of thousands of records to SSRS and then sort and filter there.
My report parameter is 'Top 3' or 'Bottom 3'. I'll then call the stored procedure and depending on the parameter will call the appropriate IF ELSE block in the stored procedure.
I'm anticipate my users will next want to dynamically set the number of top or bottom records returned. Show me top 10 rather than top 3.
I think my approach here would be limit the number of records, say 100. Hardcode 100 in my proc, always return 100 records to SSRS and then sort and filter the number of records to match my users record# parameter.
I like your approach, but it may be worth looking at the dataset filters in SSRS where you can pick top N and bottom N. Order once and use those filters in the dataset to grab the data only once from SQL Server.
Jared
CE - Microsoft
April 25, 2012 at 2:36 pm
Chrissy,
One approach to this that helps keep your code intact and allows for manipulatable result ordering is using temp tables. If you do something like the following:
CREATE PROC abc
@sortdescriptor VARCHAR(20),
@topnum INT
AS
CREATE TABLE #tmp (... insert stuff here...)
INSERT INTO #tmp SELECT ...
IF @sortdescriptor = 'column 1 ASC'
BEGIN
SELECT TOP @topnum * FROM #tmp ORDER BY 1 ASC
END
IF @sortdescriptor = 'column 2 DESC'
BEGIN
SELECT TOP @topnum * FROM #tmp ORDER BY 2 DESC
END
...
It's psuedocode but you get the idea. Keep your logic intact, use a temp table for storage, and then use the if block components to simply handle return data organization and the like. It's not always the best solution but I've used it as a code-cleanliness technique before and it's not the worst choice.
In the end, the most efficient option for something like this is usually a mix of dynamic sql inside your proc (and using sp_execute with parameters) and front end components.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply