Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002

    Unanswered: Finding the rank...

    hi all...

    i hav a table STUDENT( NAME, MARKS)
    with the following sample data..

    Name Marks
    A 120
    B 150
    C 80
    D 100
    E 120
    F 120
    G 150

    and i want to give ranks to the students based on the marks scored.. if students score same marks, then same rank will b given and the next rank will b skipped...
    i.e... i want to rank the students like this..

    name marks rank
    B 150 1
    G 150 1
    A 120 3
    E 120 3
    F 120 3
    D 100 6
    C 80 7

    Iam using oracle 8i... and i want the sql query to get the list of students for a given rank.
    can anyone help me?

    thanks in advance

  2. #2
    Join Date
    Apr 2003
    Minneapolis, MN

    I guess you can use RANK () as an analytic function to determine the rank of each student and it will skip the rank if two student has the same score.

    for e.g

    SELECT name, marks, RANK () OVER (ORDER BY marks) AS "Rank of students"
    FROM student;

    I guess You have RANK () available in 8i but it cannot be used as an analytic function. It can only be used as an aggregate functions. So the above query might not work with 8i.

    You have to make your own custom function that work just same like the one above.

    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Jan 2004
    You are absolutely right Bhavin ...

    Here a little modification is needed for fulfilling kunchesm's criterian :

    SELECT name, marks, RANK () OVER (ORDER BY marks desc) AS "Rank of students" FROM student

    ORDER BY marks desc
    is required .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts