hi all...

i hav a table STUDENT( NAME, MARKS)
with the following data..

Name Marks
---------------
A 120
B 150
C 80
D 100
E 120
F 120
G 150

and i want to give ranks to the students based on the marks scored.. if students score same marks, then same rank will b given and the next rank will b skipped...
i.e... i want to rank the students like this..

name marks rank
---------------------
B 150 1
G 150 1
A 120 3
E 120 3
F 120 3
D 100 6
C 80 7

Now i want to find the list of students for a given rank.
can anyone help me?

thanks in advance

something like this might work using case statement.

you can mess around with it but this should give you an idea.

SELECT student, mark,
'rank' = CASE When mark = 150 THEN 1
When mark = 140 THEN 2
When mark = 130 THEN 3
When mark = 120 THEN 4
ELSE 0 END
from Table

Analytical functions will help you. I do not know what database vendor you are using, but I'm pretty sure that Oracle and DB2 have rank/dense_rank functions (no idea about others)

iam using oracle 8i... and i want SQL query for getting the list of students for a given rank.

Select name, mark,
RANK() OVER (ORDER BY MARK DESC) "RANK" from table;