Re: QUERY (top 10)

Seema,

your output doesn't make sense ... if you want top 10 by domain, why
yahoo.com is listed multiple times? If you wat by domain try something
like

select *
from (select trunc(date_col), domain, count(spam_msgs),=20
                  rank () over (order by count(spam_msgs) partition by
 trunc(date_col) r)
           from my_table group by trunc(date_col), domain)
where r <=3D 10
/

of course check for syntax ...
Raj

On 5/16/05, Seema Singh <oracledbam@xxxxxxxxxxx> wrote:
> hi,
>=20
> 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
>=20
> 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
>=20
> thanks
>=20
> >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 num=
ber
> >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 <=3D 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)
> >
> >
> >
> >
> >
>=20
> --
> http://www.freelists.org/webpage/oracle-l
>=20


--=20
------------------------------
select standard_disclaimer from company_requirements where category =3D
'MANDATORY';
--
http://www.freelists.org/webpage/oracle-l

Other related posts: