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

• Original Message ----- From: "Seema Singh" <oracledbam_at_hotmail.com> To: <oracle-l_at_freelists.org> Sent: Sunday, May 15, 2005 8:39 PM Subject: QUERY (top 10)

--
http://www.freelists.org/webpage/oracle-l