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 Go to previous messageGo to next message
cookiemonster
Messages: 13864
Registered: 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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: 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 Go to previous messageGo to next message
Littlefoot
Messages: 21546
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account 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 Go to previous messageGo to next message
cookiemonster
Messages: 13864
Registered: 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 Go to previous messageGo to next message
cookiemonster
Messages: 13864
Registered: 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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: 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 Go to previous messageGo to next message
cookiemonster
Messages: 13864
Registered: 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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: 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. /forum/fa/12209/0/ but 91.percnt assign POS is 14. please advised.
  • Attachment: 122.JPG
    (Size: 47.01KB, Downloaded 396 times)

[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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: 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.
/forum/fa/12211/0/
  • Attachment: 22.JPG
    (Size: 120.05KB, Downloaded 231 times)
Re: Rank/Position Calculation. [message #625294 is a reply to message #625283] Sat, 04 October 2014 03:36 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: 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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: 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 Go to previous messageGo to next message
Littlefoot
Messages: 21546
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account 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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: 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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: 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 Go to previous messageGo to next message
cookiemonster
Messages: 13864
Registered: 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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: 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.
please see the attached file./forum/fa/12219/0/
  • Attachment: 223.JPG
    (Size: 120.05KB, Downloaded 509 times)
Re: Rank/Position Calculation. [message #625680 is a reply to message #625610] Mon, 13 October 2014 03:11 Go to previous messageGo to next message
cookiemonster
Messages: 13864
Registered: 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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: 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;
/forum/fa/12220/0/
  • Attachment: 1234.JPG
    (Size: 79.33KB, Downloaded 361 times)

[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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
Thanks for all your help.This is the result/output i have required, which i get. thanks.
/forum/fa/12224/0/
with view and main query.
  • Attachment: 223.JPG
    (Size: 75.84KB, Downloaded 177 times)

[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 Go to previous messageGo to next message
cookiemonster
Messages: 13864
Registered: 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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: 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 Go to previous messageGo to next message
cookiemonster
Messages: 13864
Registered: 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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: 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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
how i can show first three(1)rank? 1st _______________ 2nd _________________ 3rd __________________
please advised.
Re: Rank/Position Calculation. [message #627256 is a reply to message #627231] Mon, 10 November 2014 03:13 Go to previous messageGo to next message
cookiemonster
Messages: 13864
Registered: 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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
Please view the attached files. One result is on reports and the other result on SQL*Plus.
/forum/fa/12361/0/
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
    (Size: 114.30KB, Downloaded 165 times)
Re: Rank/Position Calculation. [message #629691 is a reply to message #629661] Mon, 15 December 2014 03:12 Go to previous messageGo to next message
cookiemonster
Messages: 13864
Registered: 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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: 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 Go to previous messageGo to next message
cookiemonster
Messages: 13864
Registered: 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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: 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 Go to previous messageGo to next message
cookiemonster
Messages: 13864
Registered: 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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: 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./forum/fa/12366/0/

================================================
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
    (Size: 165.57KB, Downloaded 155 times)
Re: Rank/Position Calculation. [message #629963 is a reply to message #629961] Thu, 18 December 2014 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 66928
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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.
Just think a little bit, why one should make effort to help you if you you don't help him to help you?

Re: Rank/Position Calculation. [message #629964 is a reply to message #629961] Thu, 18 December 2014 03:14 Go to previous messageGo to next message
cookiemonster
Messages: 13864
Registered: 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 Go to previous messageGo to next message
Littlefoot
Messages: 21546
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Image says:shahzad-ul-hasan

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


How about switching to DENSE_RANK?
Re: Rank/Position Calculation. [message #629967 is a reply to message #629964] Thu, 18 December 2014 03:25 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: 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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: 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 Go to previous messageGo to next message
Littlefoot
Messages: 21546
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account 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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: 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
    (Size: 114.30KB, Downloaded 182 times)
Re: Rank/Position Calculation. [message #632075 is a reply to message #632074] Fri, 23 January 2015 20:58 Go to previous messageGo to previous message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
please view the test case.
  • Attachment: testcase.txt
    (Size: 3.26KB, Downloaded 27 times)
Previous Topic: how to get the values asc order??
Next Topic: drill down reports in oracle reports10g
Goto Forum:
  


Current Time: Sun Mar 29 01:50:25 CDT 2020