December 31, 2008 at 6:19 am
Just kidding. No million dollars, but a million thanks.
Here is the problem...
I have a datalist that will be using paging. Therefore, I had to use this stored procedure to return 30 records at a time. It is pretty standard practice:
PROCEDURE [dbo].[sp_getrankings]
-- Add the parameters for the stored procedure here
@maxrows int,
@startrow int,
@sortexpression varchar(50),
@testid int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @sql nvarchar(4000)
If @sortexpression='correct'
SET @sql='SELECT Username, Correct, TotalTime
FROM(SELECT Username, Correct, TotalTime,
ROW_NUMBER() OVER(ORDER BY ' + @sortexpression + ' DESC) as id
FROM Results a WHERE testid=' + cast(@testid as nvarchar(256)) + ') as ScoreSet
WHERE id BETWEEN ' + cast(@startrow as nvarchar(10)) + ' AND ' + cast(@startrow+@maxrows -1 as nvarchar(10))
EXEC (@sql)
End
Seems easy. But here is the very difficult part. The overall record needs to be ordered by two different directions (ASC, DESC)
You see I have a column called Correct, and another called TotalTime. The results need to be sorted where the number correct desc, and the total time asc.
For example, the following table has this:
Username Correct TotalTime
Amy 9 13.5
John 8 10.9
Sam 8 14.8
Joe 9 7.6
Don 8 11.3
The table needs to be sorted in such a way that the highest number correct and the lowest time is first (lower time means a faster time, therefore it should be first)
So when sorted it should look like this:
Username Correct TotalTime
Joe 9 7.6
Amy 9 13.5
John 8 10.9
Don 8 11.3
Sam 8 14.8
However, I still need the query above to take care of the paging for my datalist:
I tried to modify the query to this:
SET @sql='SELECT Username, Correct, TotalTime
FROM(SELECT Username, Correct, TotalTime,
ROW_NUMBER() OVER(ORDER BY ' + @sortexpression + ' DESC) as id
FROM Results a WHERE testid=' + cast(@testid as nvarchar(256)) + ') as ScoreSet
WHERE id BETWEEN ' + cast(@startrow as nvarchar(10)) + ' AND ' + cast(@startrow+@maxrows -1 as nvarchar(10)) + ' ORDER BY Correct DESC, TotalTime ASC'
The bold addition didn't seem to affect the sort at all. It appeared it sorted only by the sort expression for the ROW_NUMBER OVER part.
By the way the @sortexpression="Correct". I was thinking getting rid of the ROW_NUMBER, but they may affect my paging records.
Please help, this is almost impossible for me, since I'm still a novice at mastering sql
December 31, 2008 at 6:30 am
How about allowing the sort expression to be fully defined.
here is my code with your sample data.
CREATE TABLE Results
(Username VARCHAR(100),
Correct INT,
TotalTime DECIMAL(10,2),
testid INT)
INSERT INTO Results
SELECT 'Amy',9,13.5,1 UNION ALL
SELECT 'John',8,10.9,1 UNION ALL
SELECT 'Sam',8,14.8,1 UNION ALL
SELECT 'Joe',9,7.6,1 UNION ALL
SELECT 'Don ',8,11.3,1
CREATE PROCEDURE [dbo].[sp_getrankings]
-- Add the parameters for the stored procedure here
@maxrows int,
@startrow int,
@sortexpression varchar(50),
@testid int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @sql nvarchar(4000)
--If @sortexpression='correct'
SET @sql='SELECT Username, Correct, TotalTime
FROM(SELECT Username, Correct, TotalTime,
ROW_NUMBER() OVER(ORDER BY ' + @sortexpression + ' ) as id
FROM Results a WHERE testid=' + cast(@testid as nvarchar(256)) + ') as ScoreSet
WHERE id BETWEEN ' + cast(@startrow as nvarchar(10)) + ' AND '
+ cast(@startrow+@maxrows -1 as nvarchar(10))
EXEC (@sql)
End
GO
EXEC [dbo].[sp_getrankings] 3,1,'Correct DESC, TotalTime ASC',1
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
December 31, 2008 at 4:32 pm
I have used your code, however this doesn't work either. Thanks for replying though. I hope someone could come up with a solution for this and explain it to me like I'm a three year old.
What did you mean by "fully defined?"
December 31, 2008 at 5:35 pm
I think that you need to be more explicit about what "it doesn't work" means.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 31, 2008 at 6:36 pm
I ran the query as suggested, but the result was the same as the result as my query, it only sorted it by Correct, but not by the Totaltime.
Correct needs to sorted in descending order while its corresponding time needs to be sorted in ascending order.
December 31, 2008 at 6:54 pm
I have tried Christopher's procedure and it does work as expected for me. Could you please supply data in the form of Insert statements that demonstrates the failure that you are seeing?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 1, 2009 at 6:23 am
The problem I have with his solution is that he has a predefined set. No one has a predefined set of data in the real world. My data is entered through a process of the application. I'm still new so what the heck is UNION ALL? and what is the "1" he is inserting. How does that affect the outcome? I'm confused...Please continue to help.
January 1, 2009 at 11:18 am
What is the datatype of the TotalTime column? If it is not a numeric datatype - it is not going to sort the way you think it will.
You would need to convert the datatype to a numeric datatype to sort it appropriately.
What Christopher has done is what you should have done in the first place. He created a sample table, with sample data so he could test his query against the sample data. Read the Best Practices article that I link to in my signature to see how and why that is done.
It works correctly for Christopher and Barry because they are sorting by a decimal value - which means they are getting their sort by number. Sorting by number, 7.6 comes before 13.5. Sorting by a string (char, varchar) 13.5 comes before 7.6 and that is most likely the reason this is not working for you.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 1, 2009 at 11:31 am
Mark (1/1/2009)
The problem I have with his solution is that he has a predefined set. No one has a predefined set of data in the real world.
That's a sample set of data to test the query with, based on the example data you gave in your first post.
My data is entered through a process of the application.
So replace the dummy table that Christopher created with the real table that you have
I'm still new so what the heck is UNION ALL?
Again, a method to create sample data. If you want the technical details of what it does, see books online, there's a good explanation in there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 1, 2009 at 11:43 am
Mark (1/1/2009)
The problem I have with his solution is that he has a predefined set. No one has a predefined set of data in the real world. My data is entered through a process of the application...
Yes, we do know that. This is a "Demonstration Data Set". It is used to demonstrate a poster's problem and for us to try possible solution against. Since we cannot usually get to a poster's real data, we use this instead. Usually it is the poster's responsibility (that's you) to provide this, in the correct form, but frequently, very nice people like Christopher will do it for you (reformat it), just to get the ball rolling (once only).
You can read more about why we need this demo data and how you can provide it here: http://www.sqlservercentral.com/articles/Best+Practices/61537/
...I'm still new so what the heck is UNION ALL?
"UNION [ALL]" is just a SQL operator that allows you add rows from multiple select statements together into one (bigger) data set. If the "ALL" left off, then any duplicates are removed. As this is hard-coded data and Christopher knows that there are no duplicates in his data, there was no need for this additional overhead, so he used the "ALL".
Christopher used an INSERT..SELECT..UNION ALL..SELECT.. type approach because that is known to be the most efficient way to load constant data into a table in T-SQL (much faster than multiple inserts).
... and what is the "1" he is inserting. How does that affect the outcome?
That is just the TestID that shows up in the first half of you original post, but is left out of the data that you provided. Christopher kindly added it back in so that his SQL solution would be using the same SQL columns that you original code had.
I'm confused...Please continue to help.
We will, but you have to do your part in this so that we can. If Christopher's solution does not work for you, just telling us that it does not work is not sufficient. You are going to have to tell us why.
If his solution is not returning the right data from the demo data set, then you need to list out for us exactly what it should be returning from the demo data set.
If on the other hand, it works for the demo data (that is after all based on the example data that you originally gave us), but does not return the correct values from your actual database, then you will need to provide us with a better Demonstration Data Set that better demonstrates what about Christopher's solution is not right for your needs. And of course explain to us what results you are looking for from this new set, that are different from what his solution returns on it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 1, 2009 at 11:45 am
Heh. Gail is much faster than me... 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 1, 2009 at 9:37 pm
Dear Friend,
I think U need to do the practical more carefully
see, just copy paste the below part and the result is what u looking for dear.....
CREATE TABLE Results
(Username VARCHAR(100),
Correct INT,
TotalTime DECIMAL(10,2),
testid INT)
INSERT INTO Results
SELECT 'Amy',9,13.5,1 UNION ALL
SELECT 'John',8,10.9,1 UNION ALL
SELECT 'Sam',8,14.8,1 UNION ALL
SELECT 'Joe',9,7.6,1 UNION ALL
SELECT 'Don ',8,11.3,1
select * from results
order by correct desc,totaltime asc
Let me know it worked or not?
Mithun
January 2, 2009 at 12:31 pm
Thanks for the reply, but this query will not do when you are dealing with paging on gridviews, or datalists.
January 2, 2009 at 12:38 pm
Can you give us an update on where things stand?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 2, 2009 at 12:53 pm
What are you passing as @sortexpression? Looks to me that you need to make that bigger and change what you are putting in there, because that is what is going to cause your paging to blow up.
I suspect that param simply has a list of columns in it, which is what's causing it to ROW_NUMBER "incorrectly" per your specs.
In order for your paging to work correctly - you want the dynamic SQL to return this:
ROW_NUMBER() over (order by Correct DESC, TotalTime ASC)
and NOT:
ROW_NUMBER() over (order by Correct, TotalTime DESC)
Which means your @sortExpression would need to be set to
"Correct DESC, TotalTime ASC"
in your calling code, and the dynamic call would need to yank out the static DESC from there.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply