Thread: 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.I want the rank to be displayed in the query.
Otherwise what you have mentioned would be correct.

Regards
Tanya