Re: QUERY (top 10)
- From: "Seema Singh" <oracledbam@xxxxxxxxxxx>
- To: jaromir@xxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Mon, 16 May 2005 08:43:22 -0400
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@xxxxxxxxxxxx>
>To: <oracledbam@xxxxxxxxxxx>,<oracle-l@xxxxxxxxxxxxx>
>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@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: