asked on

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?

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.

SOLUTION

brad2575

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SStory

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.

SStory

If you wanted the top percent at the top you'd of course use the DESC keyword in the order statement for qt.PercentCorrect

bolenka

ASKER

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?

ASKER CERTIFIED SOLUTION

Obadiah Christopher

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

SStory

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

bolenka

ASKER

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.

bolenka

ASKER

informaniac, any chance you could post the syntax?

bolenka

ASKER

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.

SStory

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.

bolenka

ASKER

SStory: Thank you for that extra tip. I will keep that in mind.