Home » Developer & Programmer » Reports & Discoverer » Rank/Position Calculation. (6i Reports)
Re: Rank/Position Calculation. [message #625204 is a reply to message #625203] Thu, 02 October 2014 11:29
 cookiemonster Messages: 13864Registered: September 2008 Location: Rainy Manchester Senior Member
That's a lot more like it.
I think your best bet is to calculate % and rank seperately and then join the query that gets those to your existing query.

Something like this:

```SELECT stuid, percentage, rank() over (parition by class order by percentage desc) as pos
FROM (SELECT stuid, sum(obtained marks) / sum(possible marks) * 100 as percentage
FROM .....
WHERE .....
GROUP BY stuid
)
```

Fill in the from and where as appropriate, amend column names if necessaary, create a view with that query.
Join the existing query to the view on stuid (I'm assuming it's unique)
Re: Rank/Position Calculation. [message #625206 is a reply to message #625204] Thu, 02 October 2014 12:14
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
Yes Stuid is Primary key.

```select distinct sub.sno,test1.obtmarks,test1.perct,test.examc, test.totmarks,test.class|| '/' ||test.sec sec, sub.subject,student.stuid, ' '||student.name 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 test.tid = test.tid
and student.stuid = test1.tstuid
and student.status='PRESENT'
and test.examc=:ty
and student.class=:cls
and section=:ssec

union all

select percentage, rank() over (partition by :cls order by percentage desc) as pos
from (select sum(obtmarks)/sum(totmarks) * 100 as percentage,sub.sno,test1.obtmarks,test1.perct,test.examc, test.totmarks,test.class|| '/' ||test.sec sec, sub.subject,student.stuid, ' '||student.name name
from test1,test,student,class,sub
where test1.tid=test.tid
and test1.tdate=test.tdate
and LTRIM(TEST.subject)=RTRIM(SUB.SUBJECT)
and test.class=class.cname
and class.cname=sub.cname
and student.stuid = test1.tstuid
and student.status='PRESENT'
group by stuid,sub.sno,test1.obtmarks,test1.perct,test.examc, test.totmarks,test.class|| '/' ||test.sec, sub.subject,student.stuid, ' '||student.name
)
order by sub.sno
```

Error: ora-01789 Query block has incorrect number of result columns.

[Updated on: Thu, 02 October 2014 12:59]

Report message to a moderator

Re: Rank/Position Calculation. [message #625213 is a reply to message #625206] Thu, 02 October 2014 15:00
 Littlefoot Messages: 21546Registered: June 2005 Location: Croatia, Europe Senior MemberAccount Moderator
Both SELECTs have to have the same number (and type) of columns, i.e. they should match. Yours don't.
Re: Rank/Position Calculation. [message #625215 is a reply to message #625213] Thu, 02 October 2014 18:05
 cookiemonster Messages: 13864Registered: September 2008 Location: Rainy Manchester Senior Member
Why are you using union?
It needs to be a normal join, percentage and rank need to be additional columns in the result set, not additional rows.
Re: Rank/Position Calculation. [message #625221 is a reply to message #625215] Fri, 03 October 2014 03:09
 cookiemonster Messages: 13864Registered: September 2008 Location: Rainy Manchester Senior Member
And that 2nd select shouldn't have all those columns in the select and group by, you need stuid, obtmarks and totmarks, nothing else.
Re: Rank/Position Calculation. [message #625222 is a reply to message #625215] Fri, 03 October 2014 03:12
 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,
(select percentage,rank() over (partition by class order by stuid)
as pos from (select percentage sum(obtmarks)/sum(totmarks)*100
from test,test1 where test.tid=test1.tid and test.tdate=test1.tdate
and test.class=:cls)
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
```

0ra-00921: Unexpected end of SQL Command/
Re: Rank/Position Calculation. [message #625224 is a reply to message #625222] Fri, 03 October 2014 04:24
 cookiemonster Messages: 13864Registered: September 2008 Location: Rainy Manchester Senior Member
1) If you properly formatted your queries you would probably find it a lot easier to sport mistakes
2) This bit is what's probably throwing the error:
```(select percentage,rank() over (partition by class order by stuid) AS pos
from (select percentage sum(obtmarks)/sum(totmarks)*100
from test,test1
where test.tid=test1.tid
and test.tdate=test1.tdate
and test.class=:cls)
```

Oracle has no clue what percentage is there. It's in the wrong place to be an alias.
3) I told you that the rank query needs to go in a view - reports won't like it otherwise
4) You need to join to the view query on stuid.
Re: Rank/Position Calculation. [message #625281 is a reply to message #625224] Fri, 03 October 2014 10:43
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
View is created
```select perc,rank() over (partition by class,SEC order by stuid) AS pos,class,stuid,
examc,sec
from (select sum(obtmarks)/sum(totmarks)*100 As perc, student.class,stuid,
examc,sec
from test,test1,class,sub,student
where test.tid=test1.tid
and test.tdate=test1.tdate
and test.class=student.class
and test.sec=student.section
and test1.tstuid=student.stuid
and student.STATUS='PRESENT'
and sub.subject=test.subject
and sub.cname=class.cname
and examc=test.examc
group by student.class,stuid,examc,sec)
```

This query work fine in SQL..As shown in the figure. but 91.percnt assign POS is 14. please advised.
• Attachment: 122.JPG

[Updated on: Fri, 03 October 2014 13:19]

Report message to a moderator

Re: Rank/Position Calculation. [message #625283 is a reply to message #625281] Fri, 03 October 2014 13:27
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
```SELECT DISTINCT SUB.SNO, VIE.POS,TEST1.OBTMARKS, TEST1.PERCT, TEST.EXAMC,
TEST.TOTMARKS, TEST.CLASS||'/'||TEST.SEC See, SUB.SUBJECT, STUDENT.STUID,
' '||STUDENT.NAME DIS FROM SUB, TEST1, TEST, STUDENT, CLASS, VIE
WHERE (STUDENT.STATUS = 'PRESENT'
AND TEST.EXAMC = :ty
AND STUDENT.CLASS = :cls
AND STUDENT.SECTION = :ssec)
AND ((SUB.CNAME = CLASS.CNAME)
AND (TEST.CLASS = CLASS.CNAME)
AND (TEST1.TDATE = TEST.TDATE)
AND (TEST1.TID = TEST.TID)
AND (STUDENT.STUID = TEST1.TSTUID)
AND (TEST.SUBJECT = SUB.SUBJECT))
AND VIE.examc=:ty
AND VIE.CLASS=:CLS
AND VIE.SEC=:SSEC
AND VIE.STUID=STUDENT.STUID
AND VIE.STUID=TEST1.TSTUID
ORDER BY SUB.SNO
```

View (VIE) created in order by perct desc. it shows the position 1 to lowest number of marks as shown in the figure.
• Attachment: 22.JPG
Re: Rank/Position Calculation. [message #625294 is a reply to message #625283] Sat, 04 October 2014 03:36
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
Please advised me how i can create function for POS calculation at database level.
Re: Rank/Position Calculation. [message #625295 is a reply to message #625294] Sat, 04 October 2014 04:15
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
```create function po (pos IN NUMBER)
RETURN NUMBER
IS pos NUMBER(3);
BEGIN
select perc,rank() over (partition by class,sec,examc order by perc) into pos
from (select sum(obtmarks)/sum(totmarks)*100 As perc, student.class,stuid,
examc,sec,sub.subject
from test,test1,class,sub,student
where test.tid=test1.tid
and test.tdate=test1.tdate
and test.class=student.class
and test.sec=student.section
and test1.tstuid=student.stuid
and student.STATUS='PRESENT'
and sub.subject=test.subject
and sub.cname=class.cname
and examc=test.examc
group by student.class,stuid,examc,sec,sub.subject);
RETURN(pos);
END po;
```

Function created with warnings. Result is Function is in Invalid state. When i select class, section, exam type from parameter form it will assign rank to stuid.

[Updated on: Sat, 04 October 2014 04:17]

Report message to a moderator

Re: Rank/Position Calculation. [message #625305 is a reply to message #625295] Sat, 04 October 2014 11:10
 Littlefoot Messages: 21546Registered: June 2005 Location: Croatia, Europe Senior MemberAccount Moderator
SELECT returns two values ("perc" & "rank ...") and puts them INTO a single variable ("pos"), which is wrong.

There might be other errors (not that obvious), so - if you need assistance, don't just say "created with warnings" - you have to tell the community which ones.
Re: Rank/Position Calculation. [message #625307 is a reply to message #625305] Sat, 04 October 2014 12:17
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
Select "Perc & "Rank into perc,pos . i will tell you the outcome after the function modification.
Re: Rank/Position Calculation. [message #625308 is a reply to message #625307] Sat, 04 October 2014 12:29
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
```SQL> show errors
Errors for FUNCTION PO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
1/1      PLS-00410: duplicate fields in RECORD,TABLE or argument list are
not permitted
```
Re: Rank/Position Calculation. [message #625309 is a reply to message #625308] Sat, 04 October 2014 15:05
 cookiemonster Messages: 13864Registered: September 2008 Location: Rainy Manchester Senior Member
I didn't mention anything about using a function, so I'm somewhat baffled by the fact that you're trying. It's not going to work.
This is the last time I'm going to say this:
You need a view that gives the rank for each student.
You link to that view in the main query of the report - the report will select from the view directly.
And before you try to create a view make sure that the partition by and order by you use for the rank give the results you want. You must order by percentage.
Re: Rank/Position Calculation. [message #625610 is a reply to message #625309] Sat, 11 October 2014 03:10
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
```SELECT DISTINCT SUB.SNO, VIE.POS,TEST1.OBTMARKS, TEST1.PERCT, TEST.EXAMC,
TEST.TOTMARKS, TEST.CLASS||'/'||TEST.SEC See, SUB.SUBJECT, STUDENT.STUID,
' '||STUDENT.NAME DIS FROM SUB, TEST1, TEST, STUDENT, CLASS, VIE
WHERE (STUDENT.STATUS = 'PRESENT'
AND TEST.EXAMC = :ty
AND STUDENT.CLASS = :cls
AND STUDENT.SECTION = :ssec)
AND ((SUB.CNAME = CLASS.CNAME)
AND (TEST.CLASS = CLASS.CNAME)
AND (TEST1.TDATE = TEST.TDATE)
AND (TEST1.TID = TEST.TID)
AND (STUDENT.STUID = TEST1.TSTUID)
AND (TEST.SUBJECT = SUB.SUBJECT))
AND VIE.examc=:ty
AND VIE.CLASS=:CLS
AND VIE.SEC=:SSEC
AND VIE.STUID=STUDENT.STUID
AND VIE.STUID=TEST1.TSTUID
ORDER BY SUB.SNO
```

vie is the view please check i have done correctly.
• Attachment: 223.JPG
Re: Rank/Position Calculation. [message #625680 is a reply to message #625610] Mon, 13 October 2014 03:11
 cookiemonster Messages: 13864Registered: September 2008 Location: Rainy Manchester Senior Member
You need to show us the view query.
Re: Rank/Position Calculation. [message #625687 is a reply to message #625680] Mon, 13 October 2014 04:15
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
```CREATE OR REPLACE FORCE VIEW VIE (
select perc,rank() over (partition by class,SEC order by perc DESC) AS pos,class,sec
,examc
from (select sum(obtmarks)/sum(totmarks)*100 As perc,student.class,test.examc,
test.sec
from test,test1,class,sub,student
where test.tid=test1.tid
and test.tdate=test1.tdate
and test.class=STUDENT.CLASS
and test.sec=STUDENT.SECTION
and test1.tstuid=student.stuid
and student.STATUS='PRESENT'
and sub.subject=test.subject
and sub.cname=class.cname
and examc=TEST.EXAMC
group by student.class,examc,sec,STUID)
```

THE RESULT OUTPUT AT SQL is correct. but how i can used this view in main query;
• Attachment: 1234.JPG

[Updated on: Mon, 13 October 2014 04:32]

Report message to a moderator

Re: Rank/Position Calculation. [message #625735 is a reply to message #625687] Mon, 13 October 2014 13:11
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
Thanks for all your help.This is the result/output i have required, which i get. thanks.

with view and main query.
• Attachment: 223.JPG

[Updated on: Mon, 13 October 2014 13:12]

Report message to a moderator

Re: Rank/Position Calculation. [message #625786 is a reply to message #625735] Tue, 14 October 2014 04:09
 cookiemonster Messages: 13864Registered: September 2008 Location: Rainy Manchester Senior Member
So what was the final view query and report query?
Re: Rank/Position Calculation. [message #625803 is a reply to message #625786] Tue, 14 October 2014 06:19
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
```SELECT DISTINCT SUB.SNO, TEST1.OBTMARKS, TEST.EXAMC, TEST.TOTMARKS, TEST.CLASS||'/'||TEST.SEC See, SUB.SUBJECT, STUDENT.STUID, ' '||STUDENT.NAME DIS,VIE.POS,vie.perc
FROM SUB, TEST1, TEST, STUDENT, CLASS,VIE
WHERE (STUDENT.STATUS = 'PRESENT'
AND TEST.EXAMC = :ty
AND STUDENT.CLASS = :cls
AND STUDENT.SECTION = :ssec)
AND ((SUB.CNAME = CLASS.CNAME)
AND (TEST.CLASS = CLASS.CNAME)
AND (TEST1.TDATE = TEST.TDATE)
AND (TEST1.TID = TEST.TID)
AND VIE.CLASS=:CLS
AND VIE.SEC=:SSEC
and vie.stuid=student.stuid
AND VIE.EXAMC=:TY)
AND (STUDENT.STUID = TEST1.TSTUID)
AND (TEST.SUBJECT = SUB.SUBJECT)
ORDER BY SUB.SNO
```

this is main query.
Re: Rank/Position Calculation. [message #625811 is a reply to message #625803] Tue, 14 October 2014 07:08
 cookiemonster Messages: 13864Registered: September 2008 Location: Rainy Manchester Senior Member
And the view query?
Re: Rank/Position Calculation. [message #627230 is a reply to message #625811] Sun, 09 November 2014 20:33
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
Here is the View Query:
```CREATE OR REPLACE FORCE VIEW VIE
(
PERC, POS, CLASS, SEC, EXAMC, STUID
)
AS
select perc,rank() over (partition by class,SEC,examc order by perc DESC NULLS LAST) AS pos,class,sec
,examc,stuid
from (select sum(obtmarks)/sum(totmarks)*100 As perc,student.class,test.examc,
test.sec,student.stuid
from test,test1,class,sub,student
where test.tid=test1.tid
and test.tdate=test1.tdate
and test.class=STUDENT.CLASS
and test.sec=STUDENT.SECTION
and test1.tstuid=student.stuid
and student.STATUS='PRESENT'
and sub.subject=test.subject
and sub.cname=class.cname
and examc=TEST.EXAMC
group by student.class,examc,sec,STUID)
/

```
Re: Rank/Position Calculation. [message #627231 is a reply to message #627230] Sun, 09 November 2014 20:36
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
how i can show first three(1)rank? 1st _______________ 2nd _________________ 3rd __________________
Re: Rank/Position Calculation. [message #627256 is a reply to message #627231] Mon, 10 November 2014 03:13
 cookiemonster Messages: 13864Registered: September 2008 Location: Rainy Manchester Senior Member
You need a standard top-n query:
```SELECT t.*, rownum FROM
(QUERY THAT GIVES STUDENTS WITH RANK ORDERED BY RANK DESCENDING) t
WHERE ROWNUM < 3
```
Re: Rank/Position Calculation. [message #629661 is a reply to message #627256] Sun, 14 December 2014 01:18
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
Please view the attached files. One result is on reports and the other result on SQL*Plus.

```select perc,rank() over (partition by class,SEC,EXAMC order by perc DESC NULLS LAST) AS pos,
stuid,class,sec,examc
from (select sum(obtmarks)/sum(totmarks)*100 As perc,student.class,stuid,test.examc,
test.sec
from test,test1,class,sub,student
where test.tid=test1.tid
and test.tdate=test1.tdate
and test.class=student.class
and test.sec=student.SECTION
and test1.tstuid=student.stuid
and student.STATUS='PRESENT'
and sub.subject=test.subject
and sub.cname=class.cname
and examc=test.EXAMC
group by student.class,examc,sec,STUID)
order by stuid
/
```

This query calculate the whole results of this class. for example. if class "Four" has 4 test in a month. then its calculate the whole 4 test sum of obtained and total marks of that class. i want to calculate the the specific obtained,total marks through selection of parameter form.
• Attachment: 12.JPG
Re: Rank/Position Calculation. [message #629691 is a reply to message #629661] Mon, 15 December 2014 03:12
 cookiemonster Messages: 13864Registered: September 2008 Location: Rainy Manchester Senior Member
So modify the where clause of the query to restrict to a particular test, what's the problem?
Re: Rank/Position Calculation. [message #629717 is a reply to message #629691] Mon, 15 December 2014 10:23
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
I have modified that where condition already.
```select distinct perc,rank() over (partition by class,SEC,EXAMC order by perc DESC NULLS LAST) AS pos,
stuid,class,sec,examc
from (select distinct sum(obtmarks)/sum(totmarks)*100 As perc,student.class,stuid,test.examc,
test.sec
from test,test1,class,sub,student
where test.tid=test1.tid
and test.tdate=test1.tdate
and test.class=student.class
and test.sec=student.SECTION
and test1.tstuid=student.stuid
and student.STATUS='PRESENT'
and sub.subject=test.subject
and sub.cname=class.cname
and examc=test.EXAMC
group by student.class,examc,sec,STUID)
order by stuid
```

[Updated on: Mon, 15 December 2014 10:35]

Report message to a moderator

Re: Rank/Position Calculation. [message #629718 is a reply to message #629717] Mon, 15 December 2014 10:34
 cookiemonster Messages: 13864Registered: September 2008 Location: Rainy Manchester Senior Member
Well you haven't shown us the modification you've made, so what do you expect us to do?
Re: Rank/Position Calculation. [message #629804 is a reply to message #629718] Tue, 16 December 2014 04:02
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
If i use this query with only test table. the result will be ok.
```SQL> ED
Wrote file afiedt.buf

1  SELECT SUM(TOTMARKS) FROM TEST
2* WHERE CLASS='FOUR' AND SEC='WHITE' AND EXAMC='1ST ASSESSMENT OF 2ND TERM'
SQL> /

SUM(TOTMARKS)
-------------
210
```

if i used this query;the result is ok.
```SQL> ed
Wrote file afiedt.buf

1  SELECT SUM(round(TEST1.OBTMARKS)) FROM TEST1
2  where tid in (1786,1836,1898,2055,2056,2057,2137,2138,2169)
3* and tstuid=1003
SQL> /

SUM(ROUND(TEST1.OBTMARKS))
--------------------------
185
```

but when i used in main query. it gives me percentage difference....How i can use these two queries in view main query.

[Updated on: Tue, 16 December 2014 04:45]

Report message to a moderator

Re: Rank/Position Calculation. [message #629818 is a reply to message #629804] Tue, 16 December 2014 04:50
 cookiemonster Messages: 13864Registered: September 2008 Location: Rainy Manchester Senior Member
You know the relationships between the tables, we don't (and yes you've said before but that info is smeared across various posts on various threads).
If you want us to work out where you're going wrong you need to supply a test case - create table statements, insert statments for sample data, the query you're running and the result you expect.

[Updated on: Tue, 16 December 2014 04:50]

Report message to a moderator

Re: Rank/Position Calculation. [message #629961 is a reply to message #629818] Thu, 18 December 2014 02:57
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
Respected Sir,
the test case is already posted in that message.
```CREATE OR REPLACE FORCE VIEW VIE
(
PERC, POS, CLASS, SEC, EXAMC, STUID
)
AS
select perc,rank() over (partition by class,SEC,examc order by perc DESC NULLS LAST) AS pos,
class,sec,examc,stuid
from(select sum(round(test1.obtmarks))/sum(totmarks)*100 as perc,stuid,student.class,
test.examc,test.sec
from test,test1,student,class
where test.class=student.class and test.sec=student.section and examc=test.examc
and test.tid=test1.tid
and test1.tstuid=student.stuid
and student.status='PRESENT'
and class.cname=test.class
and class.cname=student.class
and test.sec=student.section
group by test.examc,student.class,test.sec,student.stuid)
order by stuid
```

i have made few changes in my view (in above code).please view the attached file.every thing is ok. except the rank duplication.

================================================
```SQL> desc test
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TID                                       NOT NULL NUMBER(9)        --Primary key (Composite)
TDATE                                     NOT NULL DATE             --Primary Key (Composite)
CLASS                                              VARCHAR2(30)
EXAMC                                              VARCHAR2(80)
SUBJECT                                            VARCHAR2(70)
TOTMARKS                                           NUMBER(3)
SEC                                                VARCHAR2(50)

SQL> desc test1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TID                                                NUMBER(9)        -- Foreign Key
TDATE                                              DATE             -- Foreign Key
TSTUID                                             NUMBER(7)
OBTMARKS                                           NUMBER(5,2)
PERCT                                              NUMBER(6,2)
REMARK                                             VARCHAR2(300)
```
• Attachment: 11.JPG
Re: Rank/Position Calculation. [message #629963 is a reply to message #629961] Thu, 18 December 2014 03:08
 Michel Cadot Messages: 66928Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator

Quote:
the test case is already posted in that message.

Moderator opinion:
It does not matter, when you are asked for this either you give the link to the test case either you repost it.
An answer like "search it by yourself" is not acceptable, for me it is one reason I'd leave the topic and not help you.

Re: Rank/Position Calculation. [message #629964 is a reply to message #629961] Thu, 18 December 2014 03:14
 cookiemonster Messages: 13864Registered: September 2008 Location: Rainy Manchester Senior Member
shahzad-ul-hasan wrote on Thu, 18 December 2014 08:57
Respected Sir,
the test case is already posted in that message.

Which message? There's no test case anywhere in this thread.
Screenshots of data are not acceptable for a test case - we need insert statements so we can recreate the data.
Re: Rank/Position Calculation. [message #629966 is a reply to message #629961] Thu, 18 December 2014 03:21
 Littlefoot Messages: 21546Registered: June 2005 Location: Croatia, Europe Senior MemberAccount Moderator

If the percentage is same, it will assign duplicate no and skip the no 4

You used RANK analytic function:
```select
perc,
rank() over (partition by class, SEC, examc order by perc DESC NULLS LAST) AS pos,
...
```

Re: Rank/Position Calculation. [message #629967 is a reply to message #629964] Thu, 18 December 2014 03:25
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
test Case
```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');

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');

create table student (
stuid number(7) primary key,status varchar2(30),class varchar2(30),section varchar2(30),name varchar2(200),fname
varchar2(200));

Insert into student (stuid,status,class,section,name,fname)
values
(22,'PRESENT','KG','A','ALI','MMM');
Insert into student (stuid,status,class,section,name,fname)
values
(23,'PRESENT','KG','A','ABB','CMM');
Insert into student (stuid,status,class,section,name,fname)
values
(24,'PRESENT','KG','A','AII','MCC');
Insert into student (stuid,status,class,section,name,fname)
values
(25,'PRESENT','KG','A','AAA','CCC');
Insert into student (stuid,status,class,section,name,fname)
values
(26,'PRESENT','KG','A','ALL','CDC');
Insert into student (stuid,status,class,section,name,fname)
values
(27,'PRESENT','KG','A','VVV','CXWE');
```

[Updated on: Thu, 18 December 2014 03:30]

Report message to a moderator

Re: Rank/Position Calculation. [message #630056 is a reply to message #629966] Fri, 19 December 2014 01:36
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
with using DENSE_RANK. no changes in results. if the students gets equal total no and equal percentage it will assign same rank to the student and skip the next one..
Re: Rank/Position Calculation. [message #630057 is a reply to message #630056] Fri, 19 December 2014 02:03
 Littlefoot Messages: 21546Registered: June 2005 Location: Croatia, Europe Senior MemberAccount Moderator
It will? Hm, maybe your and my analytic functions work differently. Because, here's what I get: students 315 and 423 (the second and the third row in the TEST table) achieved the same total_no (75). Look what RANK returns and what DENSE_RANK returns. I wouldn't say that the result doesn't change.

```SQL> with test as
2    (select 223 stud_id, 58 total_no from dual union all
3     select 315 stud_id, 75 total_no from dual union all
4     select 423 stud_id, 75 total_no from dual union all
5     select 583 stud_id, 83 total_no from dual
6    )
7  select
8    stud_id,
9    rank() over (order by total_no) l_rank,
10    dense_rank() over (order by total_no) l_dense_rank
11  from test;

STUD_ID     L_RANK L_DENSE_RANK
---------- ---------- ------------
223          1            1
315          2            2
423          2            2
583          4            3

SQL>```
Re: Rank/Position Calculation. [message #632074 is a reply to message #630057] Fri, 23 January 2015 20:57
 shahzad-ul-hasan Messages: 561Registered: August 2002 Senior Member
in some classes it calculate the exact ranks. but in some classes its calculate the wrong ranks. just like for some classes it calculate the highest rank with low percentage and low rank with high percentage. please view that main query of the report builder.
```SELECT DISTINCT SUB.SNO, TEST1.OBTMARKS, TEST.EXAMC, TEST.TOTMARKS, TEST.CLASS||'/'||TEST.SEC See, SUB.SUBJECT, STUDENT.STUID, ' '||STUDENT.NAME DIS,VIE.POS,vie.perc
FROM SUB, TEST1, TEST, STUDENT, CLASS,VIE
WHERE (STUDENT.STATUS = 'PRESENT'
AND TEST.EXAMC = :ty
AND STUDENT.CLASS = :cls
AND STUDENT.SECTION = :ssec)
AND ((SUB.CNAME = CLASS.CNAME)
AND (TEST.CLASS = CLASS.CNAME)
AND (TEST1.TDATE = TEST.TDATE)
AND (TEST1.TID = TEST.TID)
AND VIE.CLASS=:CLS
AND VIE.SEC=:SSEC
and vie.stuid=student.stuid
AND VIE.EXAMC=:TY)
AND (STUDENT.STUID = TEST1.TSTUID)
AND (TEST.SUBJECT = SUB.SUBJECT)
ORDER BY SUB.SNO
```

VIE is the view.please advised how i can use the view as union with main query.in SQL*Plus the result(rank) is correct. but in report its calculate the wrong rank for low percentage students.
• Attachment: 12.JPG