Posted by Ed Murphy on 08/31/06 12:47

--[zainy]-- wrote:

> AA Guyz i want to calculate class position of students from a table.
> Sample data is as follows...
>
> Roll # - Name - Marks
> 1 - ABC - 60
> 2 - DEF - 60
> 3 - GHI - 57
> 4 - JKL - 55
> 5 -MNO - 50
> 6 -PQR - 53
>
> The query should return the following result.
>
> Roll # - Name - Marks - POSITION
> 1 - ABC - 60 - 1
> 2 - DEF - 60 - 1
> 3 - GHI - 57 - 3
> 4 - JKL - 55 - 4
> 5 -MNO - 50 - 5
> 6 -PQR - 53 - 6

Try the following:

select a.roll, a.name, a.marks, count(*) + 1 position
from table a
left join table b on b.marks > a.marks
group by a.roll, a.name, a.marks

[Back to original message]