Home » SQL & PL/SQL » SQL & PL/SQL » dense_rank performance
dense_rank performance [message #667453] Sat, 30 December 2017 03:20 Go to next message
shahzad-ul-hasan
Messages: 536
Registered: August 2002
Senior Member
dear i am using the following query which works some time fine but when student got same position it is skip the sma e position to second one. please view the attached query;
SELECT t.*, rownum FROM
(select perc,dense_rank() over (partition by class,SECtion,examc order by perc DESC NULLS LAST) AS pos,class,section,examc,stuid,name
from (select round((sum(obtmarks)/sum(totmarks)*100)) As perc,student.class,test.examc,
test.section,student.stuid,student.name
       from test,test1,class,TsubJ,student
       where test.tid=test1.tid
       and test.tdate=test1.tdate
       and test.class=:cls
       and test.section=:ssec
       and test1.tstuid=student.stuid
       and student.STATUS='PRESENT'
       and tsubj.Tsubj=test.subject
       and tsubj.cname=class.cname
       and examc=:ty
       group by student.class,examc,test.section,STUID,name)) t
WHERE ROWNUM <=4
please view the attached file.
/foru/forum/fa/13728/0/
  • Attachment: Untitled.jpg
    (Size: 135.53KB, Downloaded 187 times)
Re: dense_rank performance [message #667455 is a reply to message #667453] Sat, 30 December 2017 08:07 Go to previous message
BlackSwan
Messages: 26191
Registered: January 2009
Location: SoCal
Senior Member
We don't have your tables.
We don't have your data.
We don't know your requirements.
Therefore we can't run, test, debug, or improve posted code.


What is expected and desired results & why are these results correct?
Previous Topic: Cant use replication from SQL Server 2008 to Sybase?
Next Topic: Function or filter in hierarchy query
Goto Forum:
  


Current Time: Wed Oct 17 10:58:37 CDT 2018