### Thread: finding rank of the student in student table

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

2. 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 ?
3. 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.
4. 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

5. 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

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

Join Date
Sep 2003
Posts
68
Rep Power
15
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
7. No Profile Picture
Contributing User
Devshed Newbie (0 - 499 posts)

Join Date
Sep 2003
Posts
68
Rep Power
15

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