Query to display rank

[tanya_j]

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.

[Divya.B.V]

use the query below, Its in Db2

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

cheers,
Divya

[tanya_j]

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

[bmdirshad]

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

[tanya_j]

@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

[bmdirshad]

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 rownum as rank,rollno,name,marks
from (select rollno,name,marks
from a1
order by marks desc)

AND thanks FOR THAT QUERY