Friday, March 21, 2008

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.

at

4 comments:

  1. UnknownMarch 28, 2008 at 5:44 PM

    This comment has been removed by the author.

    ReplyDelete
  2. UnknownMarch 28, 2008 at 5:46 PM

    hi tanya!!!
    i tried this using transformations rather than writing a query ...chk out i took marks in desc order with sorter and ranks in ascending using ranking hope this helps

    ReplyDelete
  3. TanyaMarch 29, 2008 at 10:24 PM

    @Roopa,
    This is an interview question and the need is to write a query rather than using transformation.The hint is use of Self Join.
    Im still trying for the answer...

    ReplyDelete
  4. TanyaMarch 31, 2008 at 5:28 AM

    Hi,
    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;

    Regards
    Tanya

    ReplyDelete