Post reply

compute the rank of a record dynamically from a query

  • October 24, 2005 at 8:11 am

    #599522

    Yup, that ranks everyone... but the subquery can be used to evaluate only one of the members, speeding up the process in the meantime.

  • October 25, 2005 at 1:22 am

    #599725

    First of all  I indeed thank both of you for a good amount of response.

    Going through ur rreplies I constructed solution to my problem and David Burrows 1st response gave me most of the solution

    here is the trigger which solves my problem of rank computation

    drop table #temp

    CREATE TABLE #temp (rank int IDENTITY(1,1), username varchar(50),empno2 nvarchar(6) )

    INSERT  #temp(username,empno2)SELECT name,empno FROM [quizresults]

    WHERE quizid =(select quizid from quizresults where sno=(select max(sno)from quizresults))

    ORDER BY score DESC, datentime ASC

    update quizresults set rank2=rank from quizresults q, #temp t where q.empno=t.empno2 and

    quizid=(select quizid from quizresults where sno=(select max(sno)from quizresults))

    (column rank is renamed as rank2 ). In case of a tie in scores the person who got the score eariler in time will be awarded better rank as the time asc clause will take care of it. I am not expecting a tie in datentime value since it is differentiated upto milliseconds.

    I have transferred my confusion to u by using words like 'logged in user' etc Actually all I need is computation and updating of rank column  with each insertion in the quizresults table. The logging is nothing to do with sql but only to the ASP application

    I am sorry to have flouted forums norms by posting a image instead of the code for table definition and sample data sought by RGR 'US. Honestly I don't know there is a way (links given in his reply). I have gone throught the links (very useful) I shall take care in my subsequent posts.

    Thanks again to RGR and David .  🙂

     

  • October 25, 2005 at 1:31 am

    #599726

    I forgot to add..

    SQLservercentral.com is simply superb (forums as well as the news letter). Thank u folks out there for doing a gr8 service.

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply