Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

using rank() to rank a union all

231970Nov 15 2010 — edited Nov 15 2010
Hi
i'm doing a query with a union all (for telco trafic analysis) to count the numvber of seizure and relase of call group by hour
i want to find top hours bu i wonder where to put the rank() function
i know it should be something like that
RANK() OVER (ORDER BY count(*) desc) rang
but where ius the question
my sum is working good so i count (seizure+release) per hour
the order by may be changed to reflect the ranking



my query's like that
sorry names are in french... cause i'm french

thanks for you help

select sum(nbre),jour
from(
SELECT count(*) nbre, to_char(commdate,'DD/MM/YYYY HH24') jour
FROM echart_c_comm
where numsyst=lignepere and opid>=0 and to_char(commdate,'HH24:')<>'00:00'
group by to_char(commdate,'DD/MM/YYYY HH24')
union all
SELECT count(*) nbre, to_char(commdate+(dureeae/86400)+(dureeprise/86400),'DD/MM/YYYY HH24') jour
FROM echart_c_comm
where numsyst=lignepere and opid>=0 and to_char(commdate+(dureeae/86400)+(dureeprise/86400),'HH24:mi')<>'00:00'
group by to_char(commdate+(dureeae/86400)+(dureeprise/86400),'DD/MM/YYYY HH24'))
group by jour
order by to_date(jour,'DD/MM/YYYY HH24')
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 13 2010
Added on Nov 15 2010
1 comment
893 views