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: 537
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 195 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: 26274
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 Nov 14 07:20:18 CST 2018