Home » Developer & Programmer » Reports & Discoverer » Rank/Position Calculation. (6i Reports)
Rank/Position Calculation. Wed, 01 October 2014 02:40
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
Dear
Please look at the data and advised me how i can calculate the 1st, 2nd, 3rd position of the student.
```Class:    Five
Sno	Name	Tot	Obt marks	%
1	A	20	15.2	     76.00
2	B	20	9.5	     47.50
3	C	20	13.25	     66.25
4	D	20	7	     35.00
5	E	20	4	     20.00
6	F	20	17	     85.00
7	G	20	9	     45.00
8	H	20	5	     25.00
```

Re: Rank/Position Calculation. [message #625079 is a reply to message #625073] Wed, 01 October 2014 03:59
 cookiemonster Messages: 13894Registered: September 2008 Location: Rainy Manchester Senior Member
look at the rank analytic function.
Re: Rank/Position Calculation. [message #625081 is a reply to message #625079] Wed, 01 October 2014 04:08
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
how i can use rank function in formula column . the percentage column is calculated item.
Re: Rank/Position Calculation. [message #625082 is a reply to message #625081] Wed, 01 October 2014 04:09
 cookiemonster Messages: 13894Registered: September 2008 Location: Rainy Manchester Senior Member
Can't you calculate it in the query itself?
Re: Rank/Position Calculation. [message #625083 is a reply to message #625081] Wed, 01 October 2014 04:12
 Michel Cadot Messages: 67375Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator

If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data and show you how to do it.

Re: Rank/Position Calculation. [message #625084 is a reply to message #625082] Wed, 01 October 2014 04:20
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
```SELECT  distinct sub.sno,TEST1.OBTMARKS, TEST1.PERCT,  TEST.EXAMC, TEST.TOTMARKS, TEST.CLASS|| '/' ||TEST.SEC sec, SUB.SUBJECT, STUDENT.STUID, STUDENT.NAME
FROM sub,TEST1, TEST, STUDENT,class
WHERE LTRIM(TEST.SUBJECT)=RTRIM(SUB.SUBJECT)
AND TEST.CLASS=CLASS.cname
and class.cname=sub.cname
and TEST1.TDATE = TEST.TDATE
AND TEST1.TID = TEST.TID
AND STUDENT.STUID = TEST1.TSTUID
and student.status='PRESENT'
and test.examc=:ty
and student.class=:cls
and section=:ssec
order by sub.sno
```

```function CF_1Formula return Number is
begin
RETURN(Round((NVL(:SOBT,0)/NVL(:CS_TOT,0))*100));
end;
```

Please see the attached file...
• Attachment: 2.JPG
Re: Rank/Position Calculation. [message #625085 is a reply to message #625084] Wed, 01 October 2014 04:51
 cookiemonster Messages: 13894Registered: September 2008 Location: Rainy Manchester Senior Member
That's not a test case and it's not clear what sobt and cs_tot are.
Re: Rank/Position Calculation. [message #625092 is a reply to message #625085] Wed, 01 October 2014 06:13
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
```Create table Test (
TID   NUMBER(9),TDATE DATE, CLASS  VARCHAR2(30), EXAMC VARCHAR2(80),SUBJECT  VARCHAR2(70),
TOTMARKS NuMBER(3),SEC VARCHAR2(50),
constraints test_pk primary key (tid,tdate));
Create table test1 (
TID   NUMBER(9),TDATE DATE,TSTUID  NUMBER(7),OBTMARKS     NUMBER(5,2),PERCT  NUMBER(6,2), REMARK                                             VARCHAR2(300),
constraints test_fk fireign key (tid,tdate) references test(tid,tdate));

```
Re: Rank/Position Calculation. [message #625095 is a reply to message #625092] Wed, 01 October 2014 06:19
 Michel Cadot Messages: 67375Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator

This is awful, post it again with a correct format and add the INSERT statements for the data and the result you want for them.

If you make any effort to post your questions why should we make any effort to help you?

Re: Rank/Position Calculation. [message #625097 is a reply to message #625095] Wed, 01 October 2014 06:25
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
my google chrome crashes that why i could not load the all information. i will send it again.
Re: Rank/Position Calculation. [message #625100 is a reply to message #625097] Wed, 01 October 2014 06:36
 Michel Cadot Messages: 67375Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator

OK, we're waiting for you...

Re: Rank/Position Calculation. [message #625101 is a reply to message #625095] Wed, 01 October 2014 06:38
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
```Create table test (
tid number(9),tdate date, class varchar2(30),examc varchar2(80),subject varchar2(70),
totmarks number(3),sec varchar2(50)
constraints test_pk primary key (tid,tdate));

insert into test (tid,tdate,class,examc,subject,totmarks,sec)
values
(1,'23-SEP-2014','KG','1st Term Exams','English',50,A);

insert into test (tid,tdate,class,examc,subject,totmarks,sec)
values
(2,'23-SEP-2014','KG','1st Term Exams','Math',50,A);

insert into test (tid,tdate,class,examc,subject,totmarks,sec)
values
(3,'23-SEP-2014','KG','1st Term Exams','Urdu',40,A);

Create table test1 (
tid number(9),tdate date,tstuid number(7),obtmarks number(5,2),perct number(6,2),
remark varchar2(300),constraints test_fk foreign key (tid,tdate) references
test(tid,tdate));

insert into test1 (tid,tdate,tstuid,obtmarks,perct,remark)
values
(1,'23-SEP-2014',22,40,80.00,'Good');

insert into test1 (tid,tdate,tstuid,obtmarks,perct,remark)
values
(1,'23-SEP-2014',23,33,66.00,'Good');

insert into test1 (tid,tdate,tstuid,obtmarks,perct,remark)
values
(2,'23-SEP-2014',24,25.5,51.00,'Average');
insert into test1 (tid,tdate,tstuid,obtmarks,perct,remark)
values
(2,'23-SEP-2014',22,33,70.00,'Good');

insert into test1 (tid,tdate,tstuid,obtmarks,perct,remark)
values
(2,'23-SEP-2014',23,33,66.00,'Good');

insert into test1 (tid,tdate,tstuid,obtmarks,perct,remark)
values
(2,'23-SEP-2014',24,25,51.00,'Average');

insert into test1 (tid,tdate,tstuid,obtmarks,perct,remark)
values
(3,'23-SEP-2014',22,40,80.00,'Good');

insert into test1 (tid,tdate,tstuid,obtmarks,perct,remark)
values
(3,'23-SEP-2014',23,33,66.00,'Good');

insert into test1 (tid,tdate,tstuid,obtmarks,perct,remark)
values
(3,'23-SEP-2014',24,25.5,51.00,'Average');

```
Re: Rank/Position Calculation. [message #625104 is a reply to message #625101] Wed, 01 October 2014 06:47
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
```Create Table Class (
Cname varchar2(70) primary key);

Insert into class (cname) values ('KG');
Insert into class (cname) values ('I');
Insert into class (cname) values ('II');

Create table sub (
cname varchar2(70) references class(cname),sno number(3),subject varchar2(70));

Insert into sub (cname,sno,subject)
values
('KG',1,'English');
Insert into sub (cname,sno,subject)
values
('KG',2,'Math');
Insert into sub (cname,sno,subject)
values
('KG',3,'Urdu');
Insert into sub (cname,sno,subject)
values
('I',1,'English');
Insert into sub (cname,sno,subject)
values
('I',2,'Math');
Insert into sub (cname,sno,subject)
values
('I',3,'Urdu');

```
Re: Rank/Position Calculation. [message #625105 is a reply to message #625101] Wed, 01 October 2014 06:48
 cookiemonster Messages: 13894Registered: September 2008 Location: Rainy Manchester Senior Member
You should always test such scripts before posting them:
```SQL> Create table test (
tid number(9),tdate date, class varchar2(30),examc varchar2(80),subject varchar2(70),
totmarks number(3),sec varchar2(50)
constraints test_pk primary key (tid,tdate));  2    3    4
constraints test_pk primary key (tid,tdate))
*
ERROR at line 4:
ORA-00907: missing right parenthesis

SQL>

```
Re: Rank/Position Calculation. [message #625107 is a reply to message #625105] Wed, 01 October 2014 06:52
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
```Create table test (
tid number(9),tdate date, class varchar2(30),examc varchar2(80),subject varchar2(70),
totmarks number(3),sec varchar2(50),
constraints test_pk primary key (tid,tdate));
```
Re: Rank/Position Calculation. [message #625108 is a reply to message #625107] Wed, 01 October 2014 06:55
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
``` insert into test (tid,tdate,class,examc,subject,totmarks,sec)
values
(1,'23-SEP-2014','KG','1st Term Exams','English',50,'A');

insert into test (tid,tdate,class,examc,subject,totmarks,sec)
values
(2,'23-SEP-2014','KG','1st Term Exams','Math',50,'A');

insert into test (tid,tdate,class,examc,subject,totmarks,sec)
values
(3,'23-SEP-2014','KG','1st Term Exams','Urdu',40,'A');

```
Re: Rank/Position Calculation. [message #625110 is a reply to message #625108] Wed, 01 October 2014 07:29
 Michel Cadot Messages: 67375Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator

OK, you have test, test1, class and what is the result you want, explaining where all the result columns come from.

Re: Rank/Position Calculation. [message #625114 is a reply to message #625110] Wed, 01 October 2014 08:11
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
``` create table student (
stuid number(7) primary key,status varchar2(30),class varchar2(30),section varchar2(30),name varchar2(200),fname
varchar2(200));
```

That is main query for report.

``` select  distinct sub.sno,test1.obtmarks, test1.perct,test.examc,test.totmarks, test.class|| '/' ||test.sec,
sub.subject, student.stuid,student.name
from sub,test1, test, student,class
where LTRIM(test.subject)=RTRIM(sub.subject)
and test.class=class.cname
and class.cname=sub.cname
and test1.tdate = test.tdate
and test1.tid = test.tid
and student.studid = test.tstuid
and student.status='PRESENT'
and test.examc=:ty
and student.class=:cls
and section=:ssec
order by sub.sno

```

result required for 2.jpg. (1st position , 2nd position, 3rd position of the students) in my previous reply.
Re: Rank/Position Calculation. [message #625159 is a reply to message #625114] Thu, 02 October 2014 03:10
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
• Attachment: 3.JPG
Re: Rank/Position Calculation. [message #625160 is a reply to message #625159] Thu, 02 October 2014 03:20
 cookiemonster Messages: 13894Registered: September 2008 Location: Rainy Manchester Senior Member
what's your database version?
Re: Rank/Position Calculation. [message #625161 is a reply to message #625160] Thu, 02 October 2014 03:23
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
Oracle 9i (ver . 2) reports 6i
Re: Rank/Position Calculation. [message #625164 is a reply to message #625161] Thu, 02 October 2014 04:16
 cookiemonster Messages: 13894Registered: September 2008 Location: Rainy Manchester Senior Member
reports 6i doesn't recognize rank, oracle DB 9i does.
So create a view that does all the calculations you need then query that from the report.
Re: Rank/Position Calculation. [message #625165 is a reply to message #625164] Thu, 02 October 2014 04:17
 cookiemonster Messages: 13894Registered: September 2008 Location: Rainy Manchester Senior Member
And you can't use rank in a query against dual like that it makes no sense, you have to apply it to the base tables.
Re: Rank/Position Calculation. [message #625167 is a reply to message #625164] Thu, 02 October 2014 04:23
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
How i can calculate these calculation.i have created the view.sum up the total marka and sum obtained marks. and how i can used rank function in query;
Re: Rank/Position Calculation. [message #625169 is a reply to message #625167] Thu, 02 October 2014 04:37
 cookiemonster Messages: 13894Registered: September 2008 Location: Rainy Manchester Senior Member
What is the view query?
Have you read the documentation on rank?
Re: Rank/Position Calculation. [message #625170 is a reply to message #625169] Thu, 02 October 2014 04:41
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
```SELECT  distinct sub.sno,TEST1.OBTMARKS, TEST1.PERCT,  TEST.EXAMC, TEST.TOTMARKS, TEST.CLASS|| '/' ||TEST.SEC sec, SUB.SUBJECT, STUDENT.STUID, ' '||STUDENT.NAME name,
rank() over (partition by nvl(obtmarks,0)/nvl(totmarks,0)*100 order by stuid) aaa
FROM sub,TEST1, TEST, STUDENT,class
WHERE LTRIM(TEST.SUBJECT)=RTRIM(SUB.SUBJECT)
AND TEST.CLASS=CLASS.cname
and class.cname=sub.cname
and TEST1.TDATE = TEST.TDATE
AND TEST1.TID = TEST.TID
AND STUDENT.STUID = TEST1.TSTUID
and student.status='PRESENT'
and test.examc=:ty
and student.class=:cls
and section=:ssec
order by sub.sno
```

And the Output is (Wrong): (How it can correct..
• Attachment: 12.JPG
Re: Rank/Position Calculation. [message #625172 is a reply to message #625170] Thu, 02 October 2014 04:55
 cookiemonster Messages: 13894Registered: September 2008 Location: Rainy Manchester Senior Member
What is the criteria for determining rank?
I'm pretty sure you shouldn't be partitioning by %age.
Re: Rank/Position Calculation. [message #625173 is a reply to message #625172] Thu, 02 October 2014 04:57
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
Criteria for determining rank? partitioning by %age.
Re: Rank/Position Calculation. [message #625174 is a reply to message #625173] Thu, 02 October 2014 05:03
 Littlefoot Messages: 21599Registered: June 2005 Location: Croatia, Europe Senior MemberAccount Moderator
If you want to sort them by newly calculated rank value, why did you put
`order by sub.sno`
into the query? Try with
```select rank_value,
<all other columns you need>
from (your query goes here)
order by rank_value```
Re: Rank/Position Calculation. [message #625178 is a reply to message #625173] Thu, 02 October 2014 05:15
 cookiemonster Messages: 13894Registered: September 2008 Location: Rainy Manchester Senior Member
shahzad-ul-hasan wrote on Thu, 02 October 2014 10:57
Criteria for determining rank? partitioning by %age.

If you think that you don't know what partitioning does.
Don't explain with reference to how you think the rank function works, explain the rules in simple English.
Re: Rank/Position Calculation. [message #625183 is a reply to message #625178] Thu, 02 October 2014 08:04
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
Quote:
Criteria for determining rank? partitioning by %age.

My means is that i want to partition the query/table on percentage which i calculated by .

```function CF_1Formula return Number is
begin
RETURN(Round((NVL(:SOBT,0)/NVL(:CS_TOT,0))*100));
end;
```

:SOBT means student obtained marks and :cs_tot means total no of the 6 or 8 subjects.
Re: Rank/Position Calculation. [message #625184 is a reply to message #625183] Thu, 02 October 2014 08:18
 cookiemonster Messages: 13894Registered: September 2008 Location: Rainy Manchester Senior Member
So If you have 10 students each with a different % score they'll all have a rank of 1. Is that really what you want?
Cause that's what partitioning by % gives.
Re: Rank/Position Calculation. [message #625185 is a reply to message #625174] Thu, 02 October 2014 08:20
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
i cannot understand how i can use your query
```select rank_value,
<all other columns you need>
from (your query goes here)
order by rank_value
```
Re: Rank/Position Calculation. [message #625187 is a reply to message #625184] Thu, 02 October 2014 08:29
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
yes if the students have diffrent like that:
```S-No  Name      Eng+Urdu+math  Total(Subj)    ObtTot    %      Rank
1     A         30+40+52       200            122       61      3
2     B         40+41+70       200            151       75      2
3     C         41+42+80       200            163       82      1
4     D         41+42+80.2     200            163.2     82      1
5     E         30+30+30       200            90        45      4
```

That's the result i want.
Re: Rank/Position Calculation. [message #625190 is a reply to message #625187] Thu, 02 October 2014 08:38
 cookiemonster Messages: 13894Registered: September 2008 Location: Rainy Manchester Senior Member
You really don't like explaining rules in words do you? Example output is always helpful but it doesn't always make clear what required logic is.
You want students ranked by percentage.
The question is do subsets of students belong to different ranked sets, or should all the students in the output be in the same ranked list?
e.g.
```SQL> WITH DATA AS (SELECT CASE WHEN ROWNUM < 5 THEN 'A' ELSE 'B' END AS class,
2                ROWNUM stuid,
3                ROWNUM * 10 percentage FROM dual CONNECT BY LEVEL < 10)
4  SELECT class,
5         stuid,
6         percentage,
7         RANK () OVER ( order by percentage DESC) total_rank,
8         RANK () OVER (PARTITION BY CLASS order by percentage DESC) rank_per_class
9  FROM DATA;

CLASS      STUID PERCENTAGE TOTAL_RANK RANK_PER_CLASS
----- ---------- ---------- ---------- --------------
B              9         90          1              1
B              8         80          2              2
B              7         70          3              3
B              6         60          4              4
B              5         50          5              5
A              4         40          6              1
A              3         30          7              2
A              2         20          8              3
A              1         10          9              4

9 rows selected

SQL>
```

The rank needs to order by percentage, what isn't clear is whether you need a partition by clause and if so what it should be.
Re: Rank/Position Calculation. [message #625191 is a reply to message #625184] Thu, 02 October 2014 08:44
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
Your example is good but how i can used this query into main report query???

[Updated on: Thu, 02 October 2014 08:44]

Report message to a moderator

Re: Rank/Position Calculation. [message #625193 is a reply to message #625191] Thu, 02 October 2014 08:57
 cookiemonster Messages: 13894Registered: September 2008 Location: Rainy Manchester Senior Member
I don't really know since you still haven't really explained what you want generally, or even what the correct rank calculation is.
I know they say a picture paints a 1000 words but that's really not true here.
Rather it's a case of: a few simple sentences explaining your requirements would paint a 1000 pictures.

Something along lines of:
For each student I want to show a,b,c and d
d is calculated as e,f and g
Then I want to calculate a rank for each student in a given group (or not)

Above explanation should make no reference to summary or formula columns that we have no access to - it should refer to the appropriate columns from the tables themselves
Re: Rank/Position Calculation. [message #625196 is a reply to message #625193] Thu, 02 October 2014 10:02
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
Quote:
For each student I want to show a,b,c and d
d is calculated as e,f and g
Then I want to calculate a rank for each student in a given group (or not)

" For each student i want to show d calculate a rank for each student in a given group " that is i want
Re: Rank/Position Calculation. [message #625199 is a reply to message #625196] Thu, 02 October 2014 10:34
 cookiemonster Messages: 13894Registered: September 2008 Location: Rainy Manchester Senior Member
ok - either you start supplying proper information - it'll take more than one sentence - or I stop helping you.
I asked for a detailed explanation of all the data you want the report to display.
I have no idea what your definition of a group of students is.
Re: Rank/Position Calculation. [message #625203 is a reply to message #625199] Thu, 02 October 2014 10:52
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
I want to made a matrix with group report class wise to display the student (id,name,subject numbers, obtained marks in subjects, total obtained marks, percentage, and position in the class or(rank in class).
i am using this main report query. table structure i already provided to you.
```select  distinct sub.sno,test1.obtmarks, test1.perct,test.examc,test.totmarks, test.class|| '/' ||test.sec,
sub.subject, student.stuid,student.name
from sub,test1, test, student,class
where LTRIM(test.subject)=RTRIM(sub.subject)
and test.class=class.cname
and class.cname=sub.cname
and test1.tdate = test.tdate
and test1.tid = test.tid
and student.studid = test.tstuid
and student.status='PRESENT'
and test.examc=:ty
and student.class=:cls
and section=:ssec
order by sub.sno
```

the report format is in correct shape except only the (rank/position in the class not calculated correctly).if the percentage of the student is greater than the other students its rank/position in the class would be 1.than 2, 3......and so on. till last record of that class.if some student has equal percentage than rank/position will be decided by percentage decimal.
in this picture the detail of all subjects, students, obtained marks, subjects marks shown. and i highlight the formula column. The POS is empty. in that column the result of rank should be displayed based on %age column.
• Attachment: 2.JPG