>>>>I have create a student rank.dbf with five fields namely 1. English 2. Maths 3. Science 4. Total marks 5. Rank.
>>>>
>>>>I want to put the Rank for each student based on their Total marks secured.The total marks is out of 500.
>>>>
>>>>
>>>>For example student A secured total marks is 489 out of 500 and student B is 492 and student C is 389 and student D is 391.
>>>>
>>>>The Rank for Student B is 1 and student A is 2 and student D is 3 and student C is 4.
>>>>
>>>>How to Write source code?
>>>
>>>UPDATE rank;
>>> SET rank = q2.rank;
>>> FROM (SELECT RECNO() as rank,q1.* FROM (select student,tm FROM rank ORDER BY 2 desc) q1) q2;
>>> WHERE ranktable.student=q2.student
>>>
>>>
>>>To give credit where it is due............this is not my code. It is modified from a presentation on SQL Tips made by Brad Shulz to the SF Bay Area FoxPro user group.
>>
>>This code doesn't account for ties.
>
>
>Here's the code which will assign the same rank to equal results
>
>UPDATE rank;
> SET rank = q2.rank;
> FROM (SELECT rt2.student,rt2.tm,(SELECT COUNT(*)+1 AS rank FROM rank r1;
> WHERE r1.tm> rt2.tm) FROM rank rt2) q2;
> WHERE rank.student=q2.student
This is quite complex solution hard to digest. Thanks a lot for posting it.
If it's not broken, fix it until it is.
My Blog