Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> 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-lReceived on Mon May 16 2005 - 08:47:59 CDT
![]() |
![]() |