Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: QUERY (top 10)
Seema,
> I want report datewise top 10 spam sender domain.
I assume the basic select to get the count of spam per day is something like that (correct if inappropriate)
select trunc(time1) send_date, report_sender_domain_id, count(*) cnt
from spam
where spam is not null
group by trunc(time1), report_sender_domain_id
to get top 10 you simple use row_number() function and limit the row number to 10:
select * from (
select send_date, report_sender_domain_id, cnt,
row_number() over (partition by send_date order by cnt desc) as rn
from ( -- your basic select here
select trunc(time1) send_date, report_sender_domain_id, count(*) cnt
from spam
where spam is not null
group by trunc(time1), report_sender_domain_id
)
)
where rn <= 10
order by send_date, rn;
You may also consider the function CUME_DIST if you want top 10%
HTH Jaromir
-- http://www.freelists.org/webpage/oracle-lReceived on Sun May 15 2005 - 16:13:25 CDT
![]() |
![]() |