Re: QUERY (top 10)
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <oracledbam@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 15 May 2005 22:05:29 +0200
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
----- Original Message -----
From: "Seema Singh" <oracledbam@xxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Sunday, May 15, 2005 8:39 PM
Subject: QUERY (top 10)
--
//www.freelists.org/webpage/oracle-l
Other related posts:
