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.

do you mean you need the average of the "subjects" and then ordered by highest to lowest ?

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.

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.

okay so then have a field....
total = Sub1 + Sub2 + Sub3
then have your select be...

select * from tablename orderby total desc;

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

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