Re: QUERY (top 10)

From: rjamya <rjamya_at_gmail.com>
Date: Mon, 16 May 2005 09:02:04 -0400
Message-ID: <9177895d050516060266d0b88d@mail.gmail.com>

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_at_hotmail.com> 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_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 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_at_hotmail.com=

>


> >To: <oracle-l_at_freelists.org>

> >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

Received on Mon May 16 2005 - 09:06:44 CDT