Solved

How do I crete a ranking for a user in a SQL Server 2000 stored procedure to rank a user based on percentage of questions correct?

Posted on 2008-11-06
11
252 Views
Last Modified: 2012-08-13
I am using ASP.NET vb and SQL SERVER 2000 (not 2005). I need to create a stored procedure (or part of an existing stored procedure that will do the following:

1) Rank a user (from total users that have taken the quiz) on how many answers they have gotten correct.
2) Rank the user based on their percentage of correct answers...i.e. if john doe got 9% of his answers correct. John Doe ranks # 10 of 150 total users.

Please help. I know there are ranking functions Sql server 2005, but I am using 2000. How do I go about doing rankings this way using SQL Server 2000.

Once I have the rankings, I have to output to a page.
0
Comment
Question by:bolenka
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 16

Assisted Solution

by:brad2575
brad2575 earned 100 total points
ID: 22894802
dont have the syntax, but if you just calculate the formula for the percent they received, then order by the percent, you will have the order they need to be in.

then use something like this link to create the row number (with the order by will give you the rank)

http://databases.aspfaq.com/database/how-do-i-return-row-numbers-with-my-query.html
0
 
LVL 25

Expert Comment

by:SStory
ID: 22894994
We would need to see db structure to give you code.

I assume you have a user table.  Then I assume a quiz table, probably a questions table, etc.
Assuming that quiz table are the questions to be asked, and maybe there is a quiz_taken table that represents a user having taken a quiz.  I also assume that you have a field in the quiz_taken table that tells the percent correct. If not you'd have to do another query to get the results.

SELECT u.UserName,qt.PercentCorrect
FROM USER u
INNER JOIN Quiz_Taken qt ON u.User_ID=qt.User_ID
INNER JOIN quiz q ON qt.quiz_id=q.quiz_id
ORDER BY qt.PercentCorrect,u.UserName

based on my assumptions, you'd do something like the above.
0
 
LVL 25

Expert Comment

by:SStory
ID: 22894998
If you wanted the top percent at the top you'd of course use the DESC keyword in the order statement for qt.PercentCorrect
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:bolenka
ID: 22895320
actually I only have one table. I have a userAnswers table that is created when the program is opened and then it just updates the answer when the user submits the answer. All of the questions and answers for comparison are in one table...its just what the client had available.

ID(unique) RecordID(testquestionID) UserID UserAnswer, CorrectAnswer, DateSubmitted, Correct, Completed, etc.

I just need to do the rankings based on number of correct answers for that RecordID(for that testquestion)...once I do the calculation for the ranking and other statistics,I insert them to a table called Benchmarks for display...so really, I have the percent corrects already, I just need to rank the user by percents I guess.

If I already have the percents calculated? Do I just order them? I would have to create an ID right to show what the ranking number is?

Does that help?
0
 
LVL 20

Accepted Solution

by:
informaniac earned 300 total points
ID: 22896121
As you have mentioned you could do the following for generating the id

create a temporary table

declare @table table
(
    rank int identity(1,1),
    UserID varchar(50),
.........
)

Now insert into this @table values based on the highest percentage

So basically when u r inserting the one with highest percentage it will give u rank 1 as highest
0
 
LVL 25

Assisted Solution

by:SStory
SStory earned 100 total points
ID: 22896157
YUCK!  that is terrible design. Oh well.

Try something like this

SELECT Distinct UserID, Count(*) as NumCorrect
WHERE UserAnswer=CorrectAnswer
ORDER BY NumCorrect,UserID

Again you may want the DESC keyword to reverse the order
0
 

Author Comment

by:bolenka
ID: 22896778
Yes I know...terrible design....but there was not much I could do about it...but thanks for your responses...I will take a look at them and get back to you. Thanks again.
0
 

Author Comment

by:bolenka
ID: 22899864
informaniac, any chance you could post the syntax?
0
 

Author Closing Comment

by:bolenka
ID: 31513887
I kind of used a little bit from all suggestions to help me, but I went with the insert to temp table...that was really helpful because I had never written to a temp table before. Thanks so much. I think my rankings are working now. you guys are awesome.
0
 
LVL 25

Expert Comment

by:SStory
ID: 22904690
Temp tables are great, but can be misused also.  This is probably a good use for them.  If you have many users who will be viewing this same information again and again, you should consider caching the information for a time to lower the db load.
0
 

Author Comment

by:bolenka
ID: 22906700
SStory: Thank you for that extra tip. I will keep that in mind.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question