using rank() to rank a union all
231970Nov 15 2010 — edited Nov 15 2010Hi
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')