Query to display rank

Hi,
Suppose we have a table with fields
Rollno Name Marks
1 A 95
2 B 90
3 C 75
4 D 80
5 E 85
Write a query to display rollno,name,marks,Rank in ascending order of rank.

use the query below, Its in Db2

select rollno,name,marks from table_name order by marks asc

cheers,
Divya

Hi Divya,

Thanks for the suggestion.But Im using Oracle database
The answer for the query is as follows:
This can be done using a self join

SELECT A.ROLLNO,A.NAME,B.MARKS,RANK()OVER (ORDER BY B.MARKS DESC) FROM A1 A,A1 B WHERE A.ROLLNO=B.ROLLNO;

I tried it..Its working....
Regards
Tanya

hi,
the query posted above executes perfectly,
but remember if u r using oracle asc is default with order by clause no need to mention it
select rollno,name,marks from table_name order by marks
this will also do
check it out

@bmdirshad,
I think you didnot go thr my query properly.[B]I want the rank to be displayed[/B] in the query.
Otherwise what you have mentioned would be correct.

Regards
Tanya

HI Tanya,
sorry i just went thru this query posted by one member.

select rollno,name,marks from table_name order by marks asc

i haven't gone thru the query that u posted using rank() coz it was not avail at that time

so CHECK THIS QUERY WITHOUT USING RANK()

select[B] rownum as rank[/B],rollno,name,marks
from (select rollno,name,marks
from a1
order by marks desc)

AND thanks FOR THAT QUERY