Re: QUERY (top 10)
- From: rjamya <rjamya@xxxxxxxxx>
- To: oracledbam@xxxxxxxxxxx
- Date: Mon, 16 May 2005 09:02:04 -0400
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
> --
> //www.freelists.org/webpage/oracle-l
>=20
--=20
------------------------------
select standard_disclaimer from company_requirements where category =3D
'MANDATORY';
--
//www.freelists.org/webpage/oracle-l
Other related posts:
