INFORMATICA MADE EASY: Query to display Rank

Friday, March 21, 2008

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.

Unknown said...: This comment has been removed by the author.; March 28, 2008 at 5:44 PM
Unknown said...: 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; March 28, 2008 at 5:46 PM
Tanya said...: @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...; March 29, 2008 at 10:24 PM
Tanya said...: 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; March 31, 2008 at 5:28 AM