Re: QUERY (top 10)

hi,

I want report datewise top 10 spam sender domain. Date,domain and spam(interger ) are columns of table.This query is not working .
the output should be like

date                 domain                  spam
05/05/05         hotmail.com            120
05/05/05         yahoo.com              110
05/05/05         yahoo.com              99
05/05/05         yahoo.com              80
05/05/05         yahoo.com              79
--------------------------------------------------
05/06/05         yahoo.com              300
05/06/05         yahoo.com              250
05/06/05         yahoo.com              200

thanks

>From: "jaromir nemec" <jaromir_at_db-nemec.com>
>To: <oracledbam_at_hotmail.com>,<oracle-l_at_freelists.org>
>Subject: Re: QUERY (top 10) 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_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