compute the rank of a record dynamically from a query
-
I have a table called quizresults which gives details such as
username,score,datentime,quizid,rank.
username,quizid combination is the primarykey.
Now I want to assign a 'rank' to each user based on the score and datntime.
where score is numeric type and datentime is smalldatetime type
rank is an empty column which is to be dynamically populated /( a computed column ?)updated as and when new records get inserted.
'score desc' and 'datentime asc' arrangement is the basis for assigning ranks.
I am not able to develop a trigger for insertion to achieve the task of dynamically generating the rank.
someone pl help me with the query.
-
Why can't you use a trigger?
-
Why can't you simply Select the data in the correct order and use the application to generate the ranking (extremely simple while you display the data)??
-
Yes, I agree It would be extremely simple if we display the whole table.
However the problem is to show each user only his rank after a login through a webpage interface. He should not know the ranks of other users. The whole table is at the backend. Only logged in user's ranks are to be retrieved either through a simple query (used inside an asp script) or trigger. I am not experinced enough to construct the code for a suitable trigger.
Any more ideas please. Thanks for ur replies.
-
What's the ratio of update VS selects you'd have to do against this table?
-
yes, If i understood ur question correctly, there are more selects than updates on this table which is the backend for an asp page.. But I can not tell the exact ratio (how does it matter !?). Also I am not very seasoned in SQL ...to admit the fact.
update is needed as and when a unique user(with uniqe quizid) takes part in the online quiz with consquent insertion of his score in the database table(quizresults)
since the postions change continuously with new users taking part in the quiz, I want to provide the dynamic rank of a user accessible to him while the quiz is in force.
I am cluless how to go about. it 🙁
-
No you pretty much had the solution... Please provide the table(s) definition, sample data and the required output and we'll figure it out.
-
Here is the table definition:
Here is sample data:-
I want 'rank' to be a computed column giving the rank of the user based on 'score','datentime' and 'quizid'. That way the ranks of all users for a particular 'quizid' are computed and inserted into the 'rank' column.
I want the T-Sql (trigger) script to compute the rank column while the table is getting updated,modified.
I want to use application program(asp script) to just read off this 'rank' column and do nothing more than that while t-sql does the job of computing the value of this 'rank' column.
waiting to see a way out.
thanks.
-
Please post scripts only (don't see the pics unless you update them on a webserver server).
Are you gonna request only one line of data on those forms or the whole set is gonna be presented at some point in the application?
-
At a guess
CREATE TABLE #temp (rank int IDENTITY(1,1), username varchar(20))
INSERT INTO #temp
SELECT username FROM
WHERE quizid = @quizid
ORDER BY score DESC, datentime ASC
SELECT rank from #temp WHERE username = @username
What do you want to do in the event of ties?
Only logged in user's ranks are to be retrieved What do you mean by logged in?
Far away is close at hand in the images of elsewhere.
Anon. -
No need for a temp table here.... especially if only 1 row at the time is retrieved.
-
If there is a 'rank' column populated with results then yes, your're right, if dynamically created then no, you're not.
Far away is close at hand in the images of elsewhere.
Anon. -
Select whatever, (Select count(*) from sameBaseTable where AllTheRowsThatComeBeforeThatOne) AS Rank from BaseTable
would work great if there's never more than one row to retrieve... but still waiting for an answer on that one.
-
But you cannot count 'AllTheRowsThatComeBeforeThatOne' without ordering the data and giving each row a sequential id
Far away is close at hand in the images of elsewhere.
Anon. -
OK, remi, I stand corrected
SELECT COUNT(a.quizid)+1 AS [rank]
FROM (SELECT score, datentime FROM
x
WHERE x.quizid = @quizid AND x.username = @username) me
LEFT OUTER JOIN
a
ON a.quizid = me.quizid
AND a.score < me.score
OR (a.score = me.score AND a.datentime > me.datentime)
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply