Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Location
    India
    Posts
    4

    Unanswered: Finding the rank.......

    hi all...

    i hav a table STUDENT( NAME, MARKS)
    with the following 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

    Now i want to find the list of students for a given rank.
    can anyone help me?

    thanks in advance

  2. #2
    Join Date
    Oct 2003
    Location
    Dallas
    Posts
    76

    ....

    something like this might work using case statement.

    you can mess around with it but this should give you an idea.

    SELECT student, mark,
    'rank' = CASE When mark = 150 THEN 1
    When mark = 140 THEN 2
    When mark = 130 THEN 3
    When mark = 120 THEN 4
    ELSE 0 END
    from Table

  3. #3
    Join Date
    Dec 2002
    Posts
    134

    Re: Finding the rank.......

    Analytical functions will help you. I do not know what database vendor you are using, but I'm pretty sure that Oracle and DB2 have rank/dense_rank functions (no idea about others)

  4. #4
    Join Date
    Aug 2002
    Location
    India
    Posts
    4

    Re: Finding the rank.......

    Originally posted by chuzhoi
    Analytical functions will help you. I do not know what database vendor you are using, but I'm pretty sure that Oracle and DB2 have rank/dense_rank functions (no idea about others)
    iam using oracle 8i... and i want SQL query for getting the list of students for a given rank.

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select name, mark,
    RANK() OVER (ORDER BY MARK DESC) "RANK" from table;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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