#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    5
    Rep Power
    0

    finding rank of the student in student table


    Hi,

    I have a table for student information with the following fields.

    student id
    student name
    subject 1
    subject 2
    subject 3

    I need to write a query so that the student rank should display.

    for example :
    let this is the data I have :

    select * from student

    student id studentname subject 1 subject 2 subject 3
    1 xyz 68 78 88
    2 abc 60 80 90
    3 test 90 90 95


    Now my query should give me the rank of the students:


    student id student name Rank
    3 test 1
    1 xyz 2
    2 abc 3

    Thanks in advance.
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    5
    Rep Power
    0
    do you mean you need the average of the "subjects" and then ordered by highest to lowest ?
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    5
    Rep Power
    0
    if your "subject 1, 2, 3" are scores and you want to order them highest to lowest. then you will need to get the AVG of the scores then do an ORDERBY that AVG.
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    5
    Rep Power
    0
    I would like to have the total (Sub1+ sub2+sub3), order by total, then assign rank
    eg :
    If this is the out put that would be great.

    student id student name Rank
    3 test 1
    1 xyz 2
    2 abc 3

    Thanks for your time.
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    5
    Rep Power
    0
    okay so then have a field....
    total = Sub1 + Sub2 + Sub3
    then have your select be...

    select * from tablename orderby total desc;
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    68
    Rep Power
    12
    I'm not sure how you would like to take care of the case in which two students have the same total.
    I added student #4 and she has the same count as student #3.


    Please try (I use Oracle 9.2.0.4) :

    create table students (

    student_id NUMBER,
    studentname VARCHAR(20),
    subject1 NUMBER,
    subject2 NUMBER,
    subject3 NUMBER

    )



    insert into students values (1, 'xyz', 68, 78, 88 )
    insert into students values (2, 'abc', 60, 80, 90)
    insert into students values (3, 'test1', 90, 90, 95)
    insert into students values (4, 'test2', 89, 91, 95)


    select student_id, s,d, rownum
    from
    (
    select student_id, s,
    rank() over (partition by s order by student_id) d
    from (
    select student_id, (subject1 + subject2 + subject3) s
    from students
    )
    order by s
    )


    which returns:

    STUDENT_ID S D ROWNUM

    2 ------- 230 ------- 1 ------- 1
    1 ------- 234 ------- 1 ------- 2
    3 ------- 275 ------- 1 ------- 3
    4 ------- 275 ------- 2 ------- 4


    Hope it helps,
    Dan
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    68
    Rep Power
    12
    Please try also:

    select student_id, s, rank() over (partition by c order by s) d, rownum
    from
    (
    select student_id, s, 7 c
    from (
    select student_id, (subject1 + subject2 + subject3) s
    from students
    )
    order by s
    )

    which returns:

    STUDENT_ID S D ROWNUM

    2 ------- 230 ------- 1------- 1
    1 ------- 234 ------- 2------- 2
    3 ------- 275 ------- 3 ------- 3
    4 ------- 275 ------- 3------- 4

    Regards,
    Dan

IMN logo majestic logo threadwatch logo seochat tools logo